mysql按id和日期求和行值

a11xaf1n 于 2021-06-23 发布在 Mysql

关注(0)|答案(3)|浏览(370)

这个问题在这里已经有答案了：

如何在mysql中返回pivot表输出(9个答案）
两年前关门了。
我需要完成的是基于varchar列值和年份范围对mysql表的列值求和。也许这让人困惑，所以我举一个基本的例子：
列：id（对此无效）、水果类型、销售日期、销售类型、价格
基本查询：

SELECT * FROM fruits

给了我：

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

我需要完成的是：

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

我一直在尝试的问题是：

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;

问题是，查询返回的值是整列值good或bad的总和，而不是按水果类型匹配的值good。
我需要一些关于那个问题的建议。

sql mysql

来源：https://stackoverflow.com/questions/50600111/mysql-sum-row-value-by-id-and-date

3条答案

按热度按时间

9fkzdhlc1#

试试这个，只要为每一行添加更多的子选择。

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

赞(0）回复(0）举报 2021-06-23

vuktfyat2#

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;

请参阅SQLFiddle上的工作演示。

赞(0）回复(0）举报 2021-06-23

6psbrbz93#

使用 sum + case when condition 总结你的销售额
试试看：

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演示链接

赞(0）回复(0）举报 2021-06-23

我来回答

mysql按id和日期求和行值

3条答案

相关问题

热门标签

最新问答