I have table with following column
id (varchar) tnstime(timestamp) status (varchar) and status contain fixed values like completed ,submitted or failed.
How can I get the count in last 24 hour like below
10 - 11 --- completed 110 submited 24 failed 3
11 - 12 --- completed 125 submited 36 failed 4
13 - 14 --- completed 156 submited 37 failed 8
15 - 16 --- completed 178 submited 26 failed 3
17 - 18 --- completed 179 submited 29 failed 6
This will give you different statuses on different rows, if you want the results on the same row I'm afraid you have to write a small procedure
SELECT
DATEPART(HOUR, tnstime) AS START_HOUR,
DATEPART(HOUR, DATEADD(HOUR, 1, tnstime)) AS END_HOUR
STATUS,
COUNT(*)
FROM YOUR_TABLE
GROUP BY DATEPART(HOUR, tnstime),DATEPART(HOUR, DATEADD(HOUR, 1, tnstime)), STATUS
Let's assume your table is defined in the following way:
CREATE TABLE t
(
id varchar(10), /* possibly a PRIMARY KEY, but not sure */
tnstime timestamp,
/* Don't use a varchar if the list of status is predefined and fixed */
status ENUM ('completed', 'submited', 'failed'),
/* This should be unique in any case, so, let's make it the PK */
PRIMARY KEY (id, tnstime)
) ;
... and we have these data:
INSERT INTO t
(id, tnstime, status)
VALUES
('A0', addtime(current_date, time '03:23:00'), 'failed'),
( '1', addtime(current_date, time '10:00:00'), 'completed'),
( '2', addtime(current_date, time '10:03:00'), 'failed'),
( '3', addtime(current_date, time '10:05:00'), 'completed'),
( '4', addtime(current_date, time '10:06:00'), 'submited'),
( '5', addtime(current_date, time '10:07:00'), 'completed'),
( '6', addtime(current_date, time '11:03:00'), 'completed'),
( '7', addtime(current_date, time '11:04:00'), 'completed'),
( '8', addtime(current_date, time '11:05:00'), 'completed') ;
First approach, let's GROUP BY hour and status...
SELECT
EXTRACT(hour FROM tnstime) AS start_hour, status, count(*) AS c0
FROM
t
WHERE
/* Restrict to doday; the < current_date + interval 1 day may not be
needed, because you're supposed to know the future ;-). But just in
case time travel were possible.
Note that current_date is today at 00:00:00. Note also >= and <.
*/
tnstime >= current_date AND tnstime < current_date + interval 1 day
GROUP BY
start_hour, status
ORDER BY
start_hour, status ;
This will provide as with the following results:
start_hour | status | c0
---------: | :-------- | -------:
3 | failed | 1
10 | completed | 3
10 | submited | 1
10 | failed | 1
11 | completed | 3
Now we pivot these data, (note that the subquery q is the previous query, minus the ORDER BY) and add some formatting niceties (concat, lpad, ...) to make it look as similar as possible to your request:
SELECT
concat(lpad(start_hour, 2, '0'), ' - ', lpad(start_hour + 1, 2, '0')) AS hour_interval,
coalesce(sum(CASE WHEN status = 'completed' THEN c0 END), 0) AS completed,
coalesce(sum(CASE WHEN status = 'submited' THEN c0 END), 0) AS submited,
coalesce(sum(CASE WHEN status = 'failed' THEN c0 END), 0) AS failed
FROM
(SELECT
EXTRACT(hour FROM tnstime) AS start_hour, status, count(*) AS c0
FROM
t
WHERE
tnstime >= current_date AND tnstime < (current_date + interval 1 day)
GROUP BY
start_hour, status
) AS q
GROUP BY
hour_interval
ORDER BY
hour_interval ;
Note that the coalesce function will convert nulls (no entries) to 0.
You'll get the following result:
hour_interval | completed | submited | failed :------------ | --------: | -------: | -----: 03 - 04 | 0 | 0 | 1 10 - 11 | 3 | 1 | 1 11 - 12 | 3 | 0 | 0
You can check everything at dbfiddle here
Reference:
