CODEWITHSUNDEEP
mysqlsqlpostgresqlgroup-bypivot
ＭＫＴ
ＭＫＴ

Reputation: 15

SQL: How would I get a total count for distinct values in a column?

SQL Beginner here. Currently working on a problem with mySQL and Postgre SQL.

I'd like to get a total count for each order priority (Not_Specified, Low, Medium, High, Critical) for each state.

For example, I'd like to get a column for Texas with a number for each order priority category, and then one for the next state, and so on. Each order priority would have its own column of count per state.

This is my current query below. Can I use a subquery or do I need to use a window function? 

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

enter image description here

Upvotes: 0

Views: 95

Answers (2)

collapsar
collapsar

Reputation: 17238

This PostgreSQL query breaks down the record count by each combination of state and order priority:

  SELECT State_or_Province
       , Order_Priority
       , COUNT(*) tally
    FROM SuperStore_Main 
GROUP BY State_or_Province
       , Order_Priority
       ;

Upvotes: 1

GMB
GMB

Reputation: 222492

You seem to be looking for conditional aggregation.

In 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

In 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

Upvotes: 2

Related Questions