在groupby中选择sum with a group by

oipij1gg 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(288)

在本示例中，使用表格：

如何输出这些信息？

我正在尝试此查询，但它只是返回每个“names”的“part”行的总数。

SELECT
    NAMES
  , SUM(PART = "F001") AS SUM_F001
  , SUM(PART = "F002") AS SUM_F002
  , SUM(PART = "F003") AS SUM_F003
FROM
  MY_TABLE
GROUP BY NAMES ASC

mysql mysqli

来源：https://stackoverflow.com/questions/50076862/select-sum-with-a-group-by-inside-a-group-by

2条答案

按热度按时间

dsekswqp1#

你已经很接近你当前的查询了。
但是您需要使用下面的查询来正确透视。

SELECT 
    NAMES
  , MAX(CASE WHEN PART = 'F001' THEN QTY ELSE 0 END) AS F001
  , MAX(CASE WHEN PART = 'F002' THEN QTY ELSE 0 END) AS F002
  , MAX(CASE WHEN PART = 'F003' THEN QTY ELSE 0 END) AS F003
  , SUM(QTY) AS alias
FROM 
 FROM
  MY_TABLE
GROUP BY
  NAMES # Don't use ASC OR DESC on GROUP BY because it's deprecated
ORDER BY
 NAMES ASC

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

s5a0g9ez2#

您可以按如下方式使用查询

SELECT NAMES , SUM(CASE WHEN PART = 'F001' THEN 1 ELSE 0 END) AS F001 , SUM(CASE WHEN PART = 'F002' THEN 1 ELSE 0 END) AS F002 , SUM(CASE WHEN PART = 'F003' THEN 1 ELSE 0 END) AS F003 , SUM(QTY) AS alias FROM FROM MY_TABLE GROUP BY NAMES ORDER BY NAMES ASC

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

我来回答

在groupby中选择sum with a group by

2条答案

相关问题

热门标签

最新问答