Update table using Group By

Question

Post reply

Update table using Group By

DerbyNeal

Hall of Fame

Points: 3854
More actions
April 23, 2013 at 9:52 am

#292794

Hi,
I have a column (Total) which needs to be updated based on a calculation of 2 other columns(Assigned + Unassigned) and is Grouped By a 4th column (Delivery Date).
Before
DeliveryDateAssignedUnaasigned
2013-04-2910
2013-04-2911
2013-04-2911
2013-04-2911
2013-04-3000
2013-04-3001
2013-05-0100
After
DeliveryDateAssignedUnaasignedTotal
2013-04-29107
2013-04-29117
2013-04-29117
2013-04-29117
2013-04-30001
2013-04-30011
2013-05-01000
The combined Assigned and Unassigned values for 2013-04-29 is 7 and is to be displayed in each of the 4 rows.
Can someone show me how to do this using T-Sql please?
Thanks in advance,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Something like this??

DECLARE @t TABLE(DeliveryDate DATE,Assigned INT,Unassigned INT, Total INT)

INSERT INTO @t(DeliveryDate,Assigned,Unassigned)

VALUES

('2013-04-29',1,0),

('2013-04-29',1,1),

('2013-04-30',0,0),

('2013-04-30',0,1),

('2013-05-01',0,0);

SELECT DeliveryDate,Assigned,Unassigned,

SUM(Assigned + Unassigned) OVER(PARTITION BY DeliveryDate) Total

FROM @t

ORDER BY DeliveryDate

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

geoff5 Hall of Fame Points: 3560 More actions · Answer 2

It seems from the wording of your original post that you may want this to be a computed column in a permanent table. Unfortunately, window functions cannot be part of a computed column definition. So you will have to use a view solution (such as the one Mark suggested above) or use a trigger to update the value any time the Assigned, Unassigned, or DeliveryDate columns change.