Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
Thread: SQL query help
02-04-2010, 04:48 PM #1
- Join Date
- Feb 2010
- Thanked 0 Times in 0 Posts
SQL query help
I am doing some SQL exercises on a postgreSQL db and have stumped myself. Here are the tables involved:
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
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.
Last edited by pwd666; 02-04-2010 at 04:52 PM.