SQL - Selecting Highest Quantity Category per hour-CodePudding

I Have a table with the following columns: 1) hours [Values between 0-47] 2) Categories (Produce, Meat, Dairy) 3) Avg(Quantity). This table follows a query that has already been selected for 4 weeks of time.

How do I select the highest average quantity category for each hour? For each hour I want the best performing category. Not the best performance of each category within the hour.

Problem is each attempt either removes the category, hour, returns all of the categories for each hour portion, or doesn't select the max value of each category at all.

What I tried:

Select hour_key, category, Avg(Quantity)
From Step2Time 
Group by hour_key, category

Original Table:

Hour	Category	AVG Quantity
0	Dairy	123
23	Meat	16
45	Dairy	11

Returned Results:

Hour	Category	Quantity
0	Dairy	15
0	Meat	16
0	Dairy	13

Expected Results:

Hour	Category	Quantity
0	Dairy	15
1	Meat	8
2	Dairy	16

CodePudding user response：

You can use a subquery to identify the "highest category by hour". Then, use its results to join with the original table and get further info (columns) in the final query, like this :

SELECT      Cat.Hour, Cat.Category, Cat.Quantity
FROM        (SELECT       orig.Hour, MAX(orig.Quantity) AS MaxQuantity
             FROM         Cat orig
             INNER JOIN   (SELECT       Hour, Category, AVG(Quantity) AS MaxQuantity
                          FROM         Cat
                          GROUP BY     Hour, Category
                          ) aggr ON orig.Hour = aggr.Hour AND orig.Category = aggr.Category
             GROUP BY    orig.Hour
            )
aggr
INNER JOIN  Cat ON (Cat.Hour = aggr.Hour AND Cat.Quantity = aggr.MaxQuantity)
ORDER BY    aggr.Hour

You can see this in action here -> http://sqlfiddle.com/#!18/6e7bf9/3

CodePudding user response：

I'm still learning SQL myself so forgive me but i think you'll need to do something tricky with the COUNT function.

Maybe try something like the following:

SELECT hour_key 
,(SELECT TOP 1 category FROM Step2Time ORDER BY COUNT(Category) DESC)
, Avg(Quantity)
FROM Step2Time

If i'm right (and im happy to be wrong) this will at least order the results by the count of the category with the most frequent at the top and select the top 1 i.e the most frequent.