CODEWITHSUNDEEP
sql-server
Symone Coleman
Symone Coleman

Reputation: 49

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

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!

Upvotes: 0

Views: 624

Answers (1)

Eric
Eric

Reputation: 3257

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

Upvotes: 2

Related Questions