Select Distinct whilst adding tuples together in SQL

Question

I have a table that contains random data against a key with duplicate entries. I'm looking to remove the duplicates (a projection as it is called in relational algebra), but rather than discarding the attached data, sum it together. For example:

orderID    cost
 1          5
 1          2
 1          10
 2          3
 2          3
 3          15

Should remove duplicates from orderID whilst summing each orderID's values:

orderID    cost
 1          17       (5 + 2 + 10)
 2          6
 3          15

My assumption is I'd use SELECT DISTINCT somehow, but I don't know how I'd go about doing so. I understand GROUP BY might be able to do something but I am unsure.

There are no duplicate rows in relational algebra. You can imagine implementing projection on a relation by making a non-relation that looks like it then dropping columns then eliminating duplicate rows then making a relation that looks like that. But that's a procedure involving a non-relations. SQL tables are not relations. SQL poorly embodies relational notions. — philipxy, Dec 09 '17 at 22:34
Possible duplicate of [How does GROUP BY work?](https://stackoverflow.com/questions/7434657/how-does-group-by-work) — philipxy, Dec 09 '17 at 22:46
Please research before considering asking a question. Here any intro to GROUP BY tells you how to do this. See [ask] & the downvote arrow mouseover text. Also hits googling 'stackexchange homework'. Eg [my *1st hit after your question when googling your title*](https://stackoverflow.com/q/15512628/3404097) is an answer to this question. — philipxy, Dec 09 '17 at 22:49

score 1 · Accepted Answer · answered Dec 09 '17 at 21:18

1

This is a very basic aggregation:

SELECT orderId, SUM(cost) AS cost
FROM MyTable
GROUP BY orderId

This says, for each "orderId" grouping, sum the "cost" field and return one value per group.

answered Dec 09 '17 at 21:18

ravioli

3,749
3
14
28

score 0 · Answer 2 · answered Dec 09 '17 at 21:17

You can use the group by clause to get one row per distinct values of the column(s) you're grouping by - orderId in this case. You can the apply an aggregate function to get a result of the columns you aren't grouping by - sum, in this case:

SELECT   orderId, SUM(cost)
FROM     mytable
GROUP BY orderId

Select Distinct whilst adding tuples together in SQL

2 Answers2