MySQL SUM row value by id and date

Question

What i need to acomplish i sum a value of column of MySQL table based on VARCHAR column value and year range. Maybe that's a confusing so i'll put a basic example:

Columns: id(useless for this), fruit_type, date_of_sale, sale_type, price

Basic Query:

SELECT * FROM fruits

gives me :

  1  APPLES   2018-05-30  GOOD   50.50 
  2  APPLES   2018-05-30  BAD   -20.50 
  3  APPLES   2018-05-30  GOOD   40.00 
  4  APPLES   2018-05-30  BAD   -10.50 
  5  APPLES   2017-05-29  GOOD   39.50 
  6  APPLES   2017-05-29  BAD   -10.00 
  7  APPLES   2017-05-29  GOOD   30.00 
  8  APPLES   2017-05-29  BAD   -20.00 
  9  BANANAS  2018-05-29  GOOD   20.00 
 10  BANANAS  2018-05-29  BAD    20.00 
 11  BANANAS  2018-05-29  GOOD   10.00 
 12  BANANAS  2018-05-29  BAD    -5.00 
 13  BANANAS  2017-05-29  GOOD   50.00 
 14  BANANAS  2017-05-29  BAD    -3.00 
 15  BANANAS  2017-05-29  GOOD   10.00 
 16  BANANAS  2017-05-29  BAD    -3.00 
 17  ORANGES  2018-05-28  GOOD    5.00 
 18  ORANGES  2018-05-28  BAD    -1.00 
 19  ORANGES  2017-05-28  GOOD   10.00 
 20  ORANGES  2017-05-28  BAD    -1.00

What i need to acomplish:

  Fruit      Sales 2017    Bad Sales 2017    Total 2017    Sales 2018    Bad Sales 2018    Total 2018       
  APPLES     69.50         -30.00            39.50         90.50         -30.50            60.00            
  BANANAS    60.00         -6.00             54.00         30.00         -10.00            20.00            
  ORANGES    10.00         -1.00             9.00          5.00          -1.00             5.00

The query i've been trying is:

SELECT fruit_type AS Fruit,\
(SELECT SUM(price) FROM fruits WHERE sale_type='GOOD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31') AS Sales_2017,\
(SELECT SUM(price) FROM fruits WHERE sale_type='BAD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31') AS Bad_Sales_2017,\
(SELECT SUM(Sales_2017-Bad_Sales_2017)),\
(SELECT SUM(price) FROM fruits WHERE sale_type='GOOD' AND date_of_sale BETWEEN '2018-01-01' AND '2018-12-31') AS Sales_2018,\
(SELECT SUM(price) FROM fruits WHERE sale_type='BAD' AND date_of_sale BETWEEN '2018-01-01' AND '2018-12-31') AS Bad_Sales_2018,\
(SELECT SUM(Sales_2018-Bad_Sales_2018)),\
FROM fruits
GROUP BY fruit_type;

The problem is the values returned by the query are the total SUM of the whole column with value GOOD or BAD instead of value GOOD by fruit type match.

Need some tips for that problem plz.

Skip the subqueries. Use case expressions to do conditional aggregation instead. — jarlh, May 30 '18 at 08:46
I think you can use a coditional sum, like select sum(case when sale_type='GOOD' AND date_of_sale BETWEEN '2017-01-01' AND '2017-12-31' then price else 0) ... and so on — Mark, May 30 '18 at 08:47
Add fruit_type condition in subquery. Lets say fruits alias is f1 and in subquery fruits alias is f2 then add f1.fruit_type = f2.fruit_type. — Fahad Anjum, May 30 '18 at 08:48
Seriously consider handling issues of data display in application code — Strawberry, May 30 '18 at 09:03

Wei Lin · Accepted Answer · 2018-05-30T09:11:27.160

use sum + case when condition to summary your sales

try it:

select 
  fruit_type AS Fruit,
  sum(
    case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
      case when sale_type = 'GOOD' then price else 0 end
    else 0 end
  ) "Sales 2017",
  sum(
    case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
      case when sale_type = 'BAD' then price else 0 end
    else 0 end
  ) "Bad Sales 2017", 
  sum(
    case when date_of_sale between '2017-01-01 00:00:00' and '2017-12-31 00:00:00' then
      price
    else 0 end
  ) "Total 2017",  
  sum(
    case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
      case when sale_type = 'GOOD' then price else 0 end
    else 0 end
  ) "Sales 2018",
  sum(
    case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
      case when sale_type = 'BAD' then price else 0 end
    else 0 end
  ) "Bad Sales 2018", 
  sum(
    case when date_of_sale between '2018-01-01 00:00:00' and '2018-12-31 00:00:00' then
      price
    else 0 end
  ) "Total 2018"
FROM fruits
GROUP BY fruit_type;

SQL Fiddle Demo Link

Thx worked like magic – Elias Cort Aguelo May 30 '18 at 09:46 — Elias Cort Aguelo, May 30 '18 at 09:46

cdaiga · Answer 2 · 2018-05-30T09:43:17.903

SELECT T.FRUIT, 
  SUM(IF(T.YEAR='2017' AND T.sale_type='GOOD',T.price_per_year,0)) `Sales 2017`,
  SUM(IF(T.YEAR='2017' AND T.sale_type='BAD',T.price_per_year,0)) `Bad Sales 2017`,
  SUM(IF(T.YEAR='2017',T.price_per_year,0)) `Total 2017`,
  SUM(IF(T.YEAR='2018' AND T.sale_type='GOOD',T.price_per_year,0)) `Sales 2018`,
  SUM(IF(T.YEAR='2018' AND T.sale_type='BAD',T.price_per_year,0)) `Bad Sales 2018`,
  SUM(IF(T.YEAR='2018',T.price_per_year,0)) `Total 2018`
FROM (SELECT
  FRUIT_TYPE FRUIT,
  YEAR(date_of_sale) YEAR,
  sale_type,
  SUM(price) price_per_year
FROM fruits 
GROUP BY 
FRUIT_TYPE,
YEAR(date_of_sale),
sale_type) T
GROUP BY FRUIT
ORDER BY T.FRUIT;

See a working DEMO on SQL Fiddle.

I corrected it @Strawberry :) Thanks for the link – cdaiga May 30 '18 at 09:43 — cdaiga, May 30 '18 at 09:43

score -1 · Answer 3 · answered May 30 '18 at 08:58

Try this, just add more subselects for each row you want.

SELECT all2017.fruit_type as Fruit
,Sales_2017
,Bad_Sales_2017
from (SELECT fruit_type
,SUM(ABS(price)) Sales_2017
FROM fruits
WHERE year(date) = 2017
GROUP BY fruit_type) as all2017
join (SELECT fruit_type
,SUM(ABS(price)) Bad_Sales_2017
FROM fruits
WHERE year(date) = 2017
AND sales_type = 'BAD'
GROUP BY fruit_type) as bad2017
on all2017.fruit_type = bad2017.fruit_type

MySQL SUM row value by id and date

3 Answers3