MySQL how to use GROUP BY and BETWEEN?

Question

I have a table in mysql which contain column inv_no, inv_date and total_amt. How can I return a table like below:-

--------------------------------------------
| Hourly     | Transaction_Qty | Total_Amt |
--------------------------------------------
|00:00-00:59 |x                |y          |
|01:00-01:59 |x                |y          |
|02:00-02:59 |x                |y          |
|03:00-03:59 |x                |y          |
|and so on......

Here is my code:

Dim query As String = "
SELECT COUNT(inv_no) AS TotalTransactionQty, SUM(total_amt) AS TotalAmount
FROM sales
GROUP BY DATE(inv_date)"

Above code can only seperate by date, How can I use group by and between to seperate my table to 24 rows?

Answer 1

Try this:

SELECT Hour(inv_date) As HourOfDay 
     , COUNT(inv_no) AS TotalTransactionQty
     , SUM(total_amt) AS TotalAmount 
  FROM sales 
 GROUP 
    BY Hour(inv_date)