how to add a total column to a table in sql server

Question

I am trying to sum my column named target where measured_component is equal to a specific condition and add it to my table but am having trouble. Ultimately I want to add 4 new rows for the 4 conditions to my current table with all the columns null except for the time_value which would be the month for each total based on the condition.

I am using the below query.

select sum(TARGET) as TARGET_TOTAL
from REF_targets
where MEASURED_COMPONENT ='dispatch' 
    or MEASURED_COMPONENT='acknoweledge' 
    or MEASURED_COMPONENT= 'DRIVE' 
    or MEASURED_COMPONENT= 'ENROUTE'
group by TIME_VALUE

When I have the conditions grouped, I get a crazy number for my sum, but if I create separate queries I get the correct total.

select time_value
     , sum(TARGET) as TARGET_TOTAL
from REF_targets
where MEASURED_COMPONENT ='dispatch' 
group by TIME_VALUE

I cant select all with this query because I keep getting an error saying that I need to add ALL the columns to the group by which ultimately gives me a mirror of the data I already have for target just in a new column.

Please help,

Thanks!

always use tags to show the version of software your are using. — FLICKER
– FLICKER, Commented Jan 19, 2018 at 18:22
Seems like you want to put your MEASURED_COMPONENT as a group by of a "partition by" windowing function — Joe Phillips
– Joe Phillips, Commented Jan 19, 2018 at 18:53

Eric · Accepted Answer · 2018-01-19 19:12:54Z

2

You get a large number because you don't put MEASURED_COMPONENT in the GROUP BY. This should give you sum for each MEASURED_COMPONENT.

select TIME_VALUES, MEASURED_COMPONENT, sum(TARGET) as TARGET_TOTAL
from REF_targets
where MEASURED_COMPONENT ='dispatch' 
    or MEASURED_COMPONENT='acknoweledge' 
    or MEASURED_COMPONENT= 'DRIVE' 
    or MEASURED_COMPONENT= 'ENROUTE'
group by TIME_VALUES, MEASURED_COMPONENT

answered Jan 19, 2018 at 19:12

Eric

3,2701 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to add a total column to a table in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related