mysql - SQL：如何获得列中不同值的总数？

Question

SQL初学者在这里。目前正在解决 mySQL 和 Postgre SQL 的问题。

我想获得每个状态的每个订单优先级（Not_Specified、Low、Medium、High、Critical）的总数。

例如，我想为德克萨斯州获取一列，其中每个订单优先级类别都有一个数字，然后为下一个州提供一个数字，依此类推。每个订单优先级在每个州都有自己的计数列。

这是我当前的以下查询。我可以使用子查询还是需要使用窗口函数？

SELECT 
    Customer_ID, City, State_or_Province, Order_Date, Order_Priority, 
    ROW_NUMBER() OVER(ORDER BY City ASC, State_or_Province ASC) AS Row_N,
    COUNT(Order_Priority) OVER (Partition BY State_or_Province) AS State_Total_count

FROM SuperStore_Main 

标签： mysqlsqlpostgresqlgroup-bypivot

Answer 1

您似乎正在寻找条件聚合。

在 MySQL 中：

select
    state_or_province,
    sum(order_priority = 'Not_Specified') cnt_not_specified,
    sum(order_priority = 'Low')           cnt_low
    sum(order_priority = 'Medium')        cnt_medium
    sum(order_priority = 'High')          cnt_not_high
    sum(order_priority = 'Critical')      cnt_critical
from superstore_main
group by state_or_province

在 Postgres 中：

select
    state_or_province,
    count(*) filter(where order_priority = 'Not_Specified') cnt_not_specified,
    count(*) filter(where order_priority = 'Low')           cnt_low
    count(*) filter(where order_priority = 'Medium')        cnt_medium
    count(*) filter(where order_priority = 'High')          cnt_not_high
    count(*) filter(where order_priority = 'Critical')      cnt_critical
from superstore_main
group by state_or_province