Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1

Thread: SQL query help

  1. #1
    New to the CF scene
    Join Date
    Feb 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question SQL query help

    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:
    Code:
    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.
    Last edited by pwd666; 02-04-2010 at 05:52 PM.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •