CODEWITHSUNDEEP
sqlsql-serversql-server-2008
AuthenticReplica
AuthenticReplica

Reputation: 870

Sum of values in column by row and also selecting other columns in the result set

My current query is as follows:

select id, quantity, c, d
from table1;

This would give me example data as below:

id | quantity | c | d
---------------------
1  |    1     | x | y
1  |    3     | x | y
2  |    1     | x | y
2  |    1     | x | y

However I want to group by the ID and get the sum of the quantities to be as below:

id | quantity | c | d
---------------------
1  |    4     | x | y
2  |    2     | x | y

I tried to modify my first query to include a group by on the id and a sum on the quantities:

select id, sum(quantity), c, d
from table1
group by id;

But I got an error because the other 2 columns are not part of the group by clause. How can I include them?

Upvotes: 3

Views: 2540

Answers (2)

Yosi Dahari
Yosi Dahari

Reputation: 6999

Using OVER clause make sense here:

select id, SUM(quantity) OVER (PARTITION BY id), c, d 
from table1

Don't forget to get distinct results. If you have more than one combination of c, d you will need to state explicitly which one you want to choose.

Upvotes: 2

dev.null
dev.null

Reputation: 538

Simple add they to the group by clause:

select id, sum(quantity), c, d from table1 group by id, c, d;

Upvotes: 2

Related Questions