计算枚举类型列上的唯一行数以在 mysql 中再输出两列

Question

我有以下 mysql 表：

Item Name   Listing Fee Listing Type
watch       $0.20       LISTED
watch       $0.20       LISTED
watch       $0.30       SOLD
glasses     $0.50       LISTED
glasses     $0.50       LISTED
glasses     $0.50       LISTED
glasses     $1.00       SOLD

我需要的是下面由 SQL 分组的输出：

Item Name   Total Fee   Total Listed    Total Sold
watch       $0.70       2       1
glasses     $2.50       3       1

规则是每个“项目名称”将具有多个列表记录，其中定义了费用和列表类型。可能有两种“列表类型”[LISTED & LISTED]。卖]。

我想在表上运行查询并生成类似于提到的输出的摘要。

谢谢 维基百科

Answer 1

SELECT
    `Item Name`, 
    SUM(`Listing Fee`) AS `Total Fee`,
    SUM(CASE `Listing Type` WHEN 'LISTED' THEN 1 ELSE 0 END) AS `Total Listed`,
    SUM(CASE `Listing Type` WHEN 'SOLD' THEN 1 ELSE 0 END) AS `Total Sold`
FROM `Table Name`
GROUP BY `Item Name`

这将使用静态“列表类型”总结列表。如果需要动态列表，则必须在存储过程中构建 SQL 并执行它。

输出：

Item Name   Total Fee   Total Listed   Total Sold
watch       $0.70       2              1
glasses     $2.50       3              1

您可以更进一步：

SELECT
    `Item Name`,
    SUM(
        CASE `Listing Type`
            WHEN 'LISTED' THEN `Listing Fee`
            ELSE 0
        END
    ) AS `Total Fee Listing`,
    SUM(
        CASE `Listing Type`
            WHEN 'SOLD' THEN `Listing Fee`
            ELSE 0
        END
    ) AS `Total Fee Sold`
FROM `Table Name`
GROUP BY `Item Name`

输出：

Item Name   Total Fee Listing   Total Fee Sold
watch       $0.40               $0.30
glasses     $1.50               $1.00

Answer 2

首先 - 您应该按项目对这些记录进行分组，然后要计算总数，您可以使用一个小技巧 -

SELECT
  `Item Name`,
  SUM(`Listing Fee`) `Total Fee`,
  COUNT(IF(`Listing Type` = 'LISTED', 1, NULL)) `Total Listed`,
  COUNT(IF(`Listing Type` = 'SOLD', 1, NULL)) `Total Sold`
FROM
  mytable
GROUP BY
  `Item Name`;