计数枚举类型列的唯一行，以在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

我需要的是一个Group by SQL的输出：

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

规则是每个“项目名称”将具有多个列表记录，其中定义了费用和列表类型。可能有两种“上市类型”上市和售出。

我想在表上运行一个查询并生成摘要，如前面提到的输出。

谢谢维基

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`

这将使用静态的"Listing Types“汇总列表。如果需要动态列表，则必须在存储过程中构建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`;