Basic SQL Select statement - How Do I find the Sum?

Question

I want to find the sum and count of specific values in my table.

In my specific situation, the table is called product. The column names for the table are 'id, product_id, quantity and shipping_price.

The table contains: (Based on SELECT * FROM product;)

product_id:1    quantity:1     end_price:15     shipping_price:1
product_id:2    quantity:2     end_price:15     shipping_price:0
product_id:1    quantity:1     end_price:15     shipping_price:1
product_id:2    quantity:1     end_price:15     shipping_price:0
product_id:1    quantity:1     end_price:15     shipping_price:1
product_id:3    quantity:1     end_price:15     shipping_price:0

I need a SQL statement which will select the amount of rows with the same product_id, get the sum of the quantity, end_price and shipping_price.

For this example I want the SQL statement to returns, for item 1:

product_id:1    quantity:3    end_price:45    shipping_price:3

score 5 · Accepted Answer · answered Jun 14 '12 at 15:24

5

You can use the SUM aggregate function, grouping your results by product_id.

SELECT  product_id,
        SUM(quantity) quantity,
        SUM(end_price) end_price,
        SUM(shipping_price) shipping_price
FROM    product
GROUP BY product_id

answered Jun 14 '12 at 15:24

David M

71,481
13
158
186

score 2 · Answer 2 · answered Jun 14 '12 at 15:25

2

SELECT product_id, SUM(quantity), SUM(end_price), SUM(shipping_price) FROM product GROUP BY product_id;

answered Jun 14 '12 at 15:25

Lucas

14,227
9
74
124

score 0 · Answer 3 · answered Jun 14 '12 at 15:24

0

SELECT product_id, sum(quantity), sum(end_price), sum(shipping_price) FROM product
group by product_id

answered Jun 14 '12 at 15:24

hkutluay

6,794
2
33
53

Basic SQL Select statement - How Do I find the Sum?

3 Answers3