CODEWITHSUNDEEP
mysql
jd_py
jd_py

Reputation: 87

Is it possible to get the total count of transaction and group by 15 mins interval

I need to get a result that will display the total count of transaction type and group it with 15 mins interval based on the "Intime" column.

Sample Dataset -- Database Name is transactions

--------------------------------------
|  InTime | TransactionType | PaidAmt |
--------------------------------------
|  09:03  |     PickUp      |  10.02  |
|  09:09  |     Delivery    |  5.05   |
|  09:14  |     Delivery    |  3.99   |
|  09:15  |     Delivery    |  1.99   |
|  09:20  |     PickUp      |  10.35  |
|  09:23  |     PickUp      |  23.01  |
|  09:33  |     PickUp      |  10.06  |
|  09:44  |     Delivery    |  1.99   |
---------------------------------------

This is the desired result.

-------------------------------------------------------------------------------
| TimeFrame | NumberofPickUp | TotalPickUp | NumberofDelivery | TotalDelivery |
-------------------------------------------------------------------------------
|   09:00   |       1        |    10.02    |        2         |     9.04      |
|   09:15   |       2        |    33.36    |        1         |     1.99      |
|   09:30   |       1        |    10.06    |        1         |     1.99      |
-------------------------------------------------------------------------------

Upvotes: 0

Views: 87

Answers (1)

Nick
Nick

Reputation: 147196

You can do this by rounding down all your time values to the next lower 15 minute boundary, which you can do with 

SEC_TO_TIME(FLOOR(TIME_TO_SEC(InTime) / 900) * 900)

You can then use this value to GROUP BY, and conditional aggregation to get the totals you need:

SELECT SEC_TO_TIME(FLOOR(TIME_TO_SEC(InTime) / 900) * 900) AS TimeFrame,
       SUM(TransactionType = 'Pickup') AS `Number of Pickup`,
       ROUND(SUM(CASE WHEN TransactionType = 'Pickup' THEN PaidAmt ELSE 0 END), 2) AS `Total Pickup`,
       SUM(TransactionType = 'Delivery') AS `Number of Delivery`,
       ROUND(SUM(CASE WHEN TransactionType = 'Delivery' THEN PaidAmt ELSE 0 END), 2) AS `Total Delivery`
FROM transactions
GROUP BY TimeFrame

Output:

TimeFrame   Number of Pickup    Total Pickup    Number of Delivery  Total Delivery
09:00:00    1                   10.02           2                   9.04
09:15:00    2                   33.36           1                   1.99
09:30:00    1                   10.06           1                   1.99

Demo on dbfiddle

If there are timeframes of interest that are not present in your table, you can most easily generate the appropriate 0 values in your application code.

Upvotes: 2

Related Questions