Trouble producing a query that groups a selection of groups

group byoraclereporting

In terms of context, I believe that all that is needed is to know that I have a table, t_sale, that logs sales made. It logs a few things, such as day and location of sale in the shop, but what is important for this is that sale_time and sale_value (arbitrary categories for the sale price, LOW, MED, and HIGH) are logged.

The problem I am having is that I need to produce a report that breaks down sales in each hour, for each value category. I have successfully done both of these things individually with. For getting the number of sales by hour:

SELECT EXTRACT(hour FROM sale_time) AS Hour, COUNT(sale_time) AS Sales
FROM t_sale
GROUP BY EXTRACT(hour FROM sale_time)
ORDER BY Sales DESC;

which results in this (shop is open 0900 to 1700):

*HOUR*  *SALES*
13      99
16      99
9       94
10      91
11      89
15      87
12      82
14      79

And producing a report for sales by value, I used this query to get the data and columns I needed:

SELECT sale_value AS "Sale Value", COUNT(sale_value) AS "Total Sales"
FROM t_sale
GROUP BY sale_value
HAVING sale_value IN ('HIGH', 'MED', 'LOW');

which results in this:

*VALUE* *SALES*
HIGH    241
LOW     244
MED     235

What I am imaging is being requested is something that, in place of sales for the values example, further breaks it down by each hour. Something like:

*VALUE*    *HOUR*    *SALES*
HIGH       09        22
           10        14
           11        31
           etc...    etc...

MED        09        52
           10        16
           11        19
           etc...    etc...

LOW        09        63
           10        33
           11        28
           etc...    etc...

I've used dbForge Studio 2020 for Oracle for the report on the values due to no end of issues with Oracle Forms and Reports. I'm confident I can make the report if I knew what query to use to generate the data. However I am completely at a loss as to what functions to use.

Best Answer

Wouldn't this work?

SELECT 
  sale_value AS "Sale Value", 
  EXTRACT(hour FROM sale_time) AS Hour,
  COUNT(sale_value) AS "Total Sales"
FROM t_sale
GROUP BY sale_value, EXTRACT(hour FROM sale_time)
HAVING sale_value IN ('HIGH', 'MED', 'LOW');
Related Question