1

I am trying to write a SQL query that will return the top items for each company and for each location. I have an example MySQL table (table_x) that looks like this:

Date      | Company | Location | Item         | Price | Quantity | Total_Amount   
----------|---------|----------|--------------|-------|----------|-------------  
1/10/2000 | ABC     | 1        | Food         | 2     | 6        | 12    
1/11/2000 | ABC     | 1        | Food         | 1     | 2        | 2  
1/12/2000 | ABC     | 2        | Food         | 10    | 5        | 50    
1/13/2000 | ABC     | 2        | Electronics  | 100   | 2        | 200  
1/10/2000 | ABC     | 1        | Consumables  | 10    | 5        | 50    
1/15/2000 | ABC     | 2        | Electronics  | 100   | 3        | 300  
1/10/2000 | DEF     | 1        | Electronics  | 50    | 5        | 250    
1/16/2000 | DEF     | 1        | Electronics  | 50    | 4        | 200  
1/19/2000 | DEF     | 2        | Food         | 10    | 5        | 50    
1/14/2000 | DEF     | 2        | Food         | 2     | 10       | 20  
1/11/2000 | DEF     | 2        | Food         | 5     | 8        | 40    
1/11/2000 | DEF     | 2        | Electronics  | 500   | 2        | 1000

And for example what I want is to return is the top item by count per company per location. So something like this where the top item by count is per company and per location.

Company | Location | Item        | AVG(Price) | SUM(Total_Amount) | COUNT(*)  
--------|----------|-------------|------------|-------------------|---------  
ABC     | 1        | Food        | 4          | 14                | 2  
ABC     | 2        | Electronics | 100        | 500               | 2  
DEF     | 1        | Electronics | 50         | 450               | 2  
DEF     | 2        | Food        | 5.67       | 110               | 3

I know how to do this across all company and locations, but have trouble getting the top items by count to be within each specific grouping. Ideally, I'd want to be able to extend this to top N items if I have more item types if possible.

This is the SQL query I ran to generate top items based on the occurrences.

SELECT Company, Location, Item, AVG(Price), SUM(Total_Amount), COUNT(*) FROM table_x  
GROUP BY Company, Location, Item   
ORDER BY Company, Location, COUNT(*) desc
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Quest
  • 15
  • 3
  • when is something a top item? Also add what you have tried. – Jeffrey Sep 03 '17 at 20:26
  • Hi Jeffrey, a top item is one that occurred most frequently regardless of price, quantity or total. So in the above example of the result I'm looking for, company ABC and location 1 top item was food because it occurred twice within that group whereas electronics and consumables only once. I edited to add the query that I ran, but that only gives me top items based on count on the whole dataset. Lost on where to refine the statement to do this within groupings if that makes sense. – Quest Sep 03 '17 at 21:17

1 Answers1

1

Use MySQL's non-standard grouping feature:

select * from (
    SELECT Company, Location, Item, AVG(Price), SUM(Total_Amount), COUNT(*) FROM table_x  
    GROUP BY Company, Location, Item   
    ORDER BY Company, Location, COUNT(*) desc
)
group by 1,2

With MySQL (only) when you omit non-aggregate columns from the group by list, the first row of each combination is returned.

Note that since version 5.7.5, you must disable ONLY_FULL_GROUP_BY, which is enabled by default.

@Jeffrey has kindly provided an SQLFiddle.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    You beat me by 2 min... here is a [SQL fiddle](http://sqlfiddle.com/#!9/36ee39/25) – Jeffrey Sep 03 '17 at 21:53
  • @jeff thanks. I thought SQLFiddle was dead for good. It wasn't working for about a year. Good to see it's back up. – Bohemian Sep 03 '17 at 22:00
  • This looks great! But what happens if I want to include the aggregate columns. How would the query change? – Quest Sep 04 '17 at 00:46
  • @quest there are no aggregate columns *in the outer query*. ie when columns are selected by the outer select, the columns have just plain values in them - notice `select *`. If I have misunderstood, pleas explain more about what you want. – Bohemian Sep 04 '17 at 01:04
  • @Bohemian sorry actually I misunderstood. So I ran into a problem running this query attempting to get the "top 10" items within each group `select * from (SELECT company, location, item, AVG(price), SUM(total_amount), COUNT(*) FROM table_x GROUP BY company, location, item ORDER BY company, location, COUNT(*) desc limit 10) group by 1,2`. The error is a SQL compilation error: ["values".ITEM] is not a valid group by expression. Not sure what that means. Also to note, the actual table I'm working with is much much larger (100 mil rows). – Quest Sep 04 '17 at 01:26