pwd666
02-04-2010, 04:48 PM
Hi everybody,
I am doing some SQL exercises on a postgreSQL db and have stumped myself. Here are the tables involved:
cust_hist
customerid prod_id
products
prod_id category
categories
category categoryname
Here is what I came up with, but its not the results I'm looking for:
SELECT distinct(customerid),max(cat_count),cat_name AS top_count FROM(
SELECT customerid,count(c.categoryname) AS cat_count,c.categoryname AS cat_name
FROM cust_hist AS ch
JOIN products AS p
ON ch.prod_id = p.prod_id
JOIN categories AS c
ON p.category = c.category
GROUP BY customerid,c.categoryname
ORDER BY customerid DESC
)AS subA GROUP BY customerid,cat_count,cat_name ORDER BY customerid
The problem with the above is that its displaying the count per category of ALL the categories for each customer.
What I would like to do is see what category each customer has purchased the most.
I'd like to see customerid, the count of items in the most purchased category, and the category name. Thanks in advance.
I am doing some SQL exercises on a postgreSQL db and have stumped myself. Here are the tables involved:
cust_hist
customerid prod_id
products
prod_id category
categories
category categoryname
Here is what I came up with, but its not the results I'm looking for:
SELECT distinct(customerid),max(cat_count),cat_name AS top_count FROM(
SELECT customerid,count(c.categoryname) AS cat_count,c.categoryname AS cat_name
FROM cust_hist AS ch
JOIN products AS p
ON ch.prod_id = p.prod_id
JOIN categories AS c
ON p.category = c.category
GROUP BY customerid,c.categoryname
ORDER BY customerid DESC
)AS subA GROUP BY customerid,cat_count,cat_name ORDER BY customerid
The problem with the above is that its displaying the count per category of ALL the categories for each customer.
What I would like to do is see what category each customer has purchased the most.
I'd like to see customerid, the count of items in the most purchased category, and the category name. Thanks in advance.