How do i select a sum of distinct columns without adding the whole column

Question

a.id  c_id  p_type  paid     date
1      6    FLOUR   100.00  2015-06-22
2      7    OIL     50.00   2015-06-21
3      6    FLOUR   242.00  2015-06-24
4      6    FLOUR   392.00  2015-06-26
5      7    OIL     200.00  2015-07-29
6      7    OIL     300.00  2015-07-25

i have been trying to select only the sum of values from distinct tables, i tried

SELECT customer.first_name, customer.last_name, credit.quantity, 
       credit.date_supplied, credit.unit_price, credit.p_type,
       ROUND(SUM(account.amount_paid))
AS amount_paid, account.date_paid FROM credit, customer 
LEFT OUTER JOIN account 
ON account.c_id = customer.c_id ;

I want to display the sum total of columns with product type flour and then that of oil but i only get a sum of the whole amount in the table. I would appreciate the help.

http://stackoverflow.com/questions/11782292/sumdistinct-based-on-other-columns — Shivam, Jul 01 '15 at 00:58

score 0 · Answer 1 · answered Jul 01 '15 at 00:35

0

You can use group by type and then use sum on the group.

answered Jul 01 '15 at 00:35

Ramzy

6,948
6
18
30

Cohan · Answer 2 · 2015-07-01T01:24:05.110

0

Group by will group things and then perform the operations on the given group. So for this, it will evaluate to split it into two groups, oil and flour, and then perform the sum on each.

SELECT 
  customer.first_name
, customer.last_name
, credit.quantity
, credit.date_supplied
, credit.unit_price
, credit.p_type
, ROUND(SUM(account.amount_paid)) AS amount_paid
, account.date_paid 
FROM credit
LEFT JOIN customer on credit.??? = customer.??? 
LEFT JOIN account ON account.c_id = customer.c_id 
GROUP BY credit.p_type;

edited Jul 01 '15 at 01:24

answered Jul 01 '15 at 01:01

Cohan

4,384
2
22
40

i tried and it still is not working, it give a sum of the whole account paid column – Peter Jul 01 '15 at 01:10
try being more explicit with your joins for credit and customer. see if that helps. – Cohan Jul 01 '15 at 01:24
Thank you that was helpful. – Peter Jul 01 '15 at 07:43
Were you able to make it work, or is there more to be done? – Cohan Jul 01 '15 at 12:37

How do i select a sum of distinct columns without adding the whole column

2 Answers2