sql How to select most popular item for each state

user_11702075 0
Viewed 1025

Table Schemas:

customers
|_state

products
|_product_name

transactions
|_product_id

How to return names of most popular item in every state w/ state name?

This is the closest working example. 

SELECT state, products.product_name, COUNT(products.product_name)
FROM customers INNER JOIN transactions ON customers.customer_id = transactions.customer_id
INNER JOIN products ON transactions.product_id=products.product_id
GROUP BY state, products.product_name;

which gives all products for each state like so:

 state      name        name count  
 "AK"    "product1"        "1"
 "AK"    "product2"        "4"
 "AK"    "product3"        "1"
 "AR"    "product2"        "1"
 "AR"    "product2"        "2"

Again, I only want the highest count per state.

greatest-n-per-group group-by postgresql
1 Answers

Using MAX() with the given query will give your expected result, the highest count per the state.

The working query will be:

SELECT state, MAX(PCount) AS HighestCount
FROM (
    SELECT state, products.product_name, COUNT(products.product_name) AS PCount
    FROM customers 
    INNER JOIN transactions ON customers.customer_id = transactions.customer_id
    INNER JOIN products ON transactions.product_id = products.product_id
    GROUP BY state, products.product_name
) Q
GROUP BY state
user_24517260
Related