MySQL: Output ID based on SUM data in one column based attributes in another column-CodePudding

SQL Table:

Customer	Type	Payment
1	Apples	5
1	Apples	5
1	Oranges	1
1	Oranges	2
2	Apples	7
2	Oranges	3
2	Oranges	6

Based on the above, looking to determine which customers have paid more for apples compared to oranges as a sum of all their payments.

In the case of the above table,

Customer 1 - Apples 10 > Oranges 3

Customer 2 - Apples 7 < Oranges 9

Thus the SQL should output Customer 1

I have attempted multiple queries, with the following as the most promising but getting an invalid use of group function error code 1111.

SELECT a.customer 
FROM (SELECT customer, SUM(payment) AS orangespaid FROM table
    WHERE type ='Oranges'
    GROUP BY customer) o
JOIN table AS a ON a.customer = o.customer
WHERE type = 'Apples' and SUM(payment) > orangespaid
GROUP BY customer
ORDER BY customer;

CodePudding user response：

Try moving the SUM into a second subquery instead

SELECT a.customer 
FROM (SELECT customer, SUM(payment) AS orangespaid FROM table
    WHERE type ='Oranges'
    GROUP BY customer) o
JOIN (SELECT customer, SUM(payment) AS applespaid FROM table
    WHERE type ='Apples'
    GROUP BY customer) AS a ON a.customer = o.customer
WHERE applespaid > orangespaid
ORDER BY customer;

CodePudding user response：

You should try with sum(case when) for each type you want, it might not the best solution but it works.

select a.customer
from (select as1.Customer, 
sum(case when type = 'Oranges' then payment else 0 end) AS orangespaid, 
sum(case when type = 'Apples' then payment else 0 end) AS applespaid
from as1 group by as1.Customer) a
where applespaid > orangespaid

dbfiddle here

CodePudding user response：

There are a lot of ways to achieve that.

Here's how you do without sub-query:

SELECT Customer,
       SUM(CASE WHEN Type='Apples' THEN Payment ELSE 0 END) AS Apples,
       SUM(CASE WHEN Type='Oranges' THEN Payment ELSE 0 END) AS Oranges
  FROM table1
  GROUP BY Customer
HAVING Apples > Oranges;

Or like this:

SELECT Customer,
      SUM(IF(Type='Apples',Payment,0)) > SUM(IF(Type='Oranges',Payment,0)) Chk
  FROM table1
  GROUP BY Customer
HAVING Chk=1

Or a slight modification of the query above, instead of checking the value in SELECT then filter from HAVING, why not just directly do the checking in HAVING:

SELECT Customer
  FROM table1
  GROUP BY Customer
HAVING SUM(IF(Type='Apples',Payment,0)) > SUM(IF(Type='Oranges',Payment,0)) != 0;

Similarly the first query can also be done the same way.

Demo fiddle

Side note:
As for the difference between using CASE or IF, it's basically operates the same so it's more to individual preference. I mostly opt to use CASE because of readability and easier to edit (not much usage of parentheses/brackets) but using IF almost every time is shorter to write.