Count data with different table

Question

i have 2 table (device_a & device_b), i want to :

union the table to and count the data for each table then group by date hour.
after that i want to count data for all device then group by date hour. there is no key for join table.
the result show the id based on order by date hour

device_a

datetime             
2020-09-23 16:00:00   
2020-09-23 16:00:00   
2020-09-23 16:00:00   
2020-09-23 17:00:00

device_b

datetime             
2020-09-23 16:00:00   
2020-09-23 16:00:00   
2020-09-23 17:00:00
2020-09-23 17:00:00

expected result:

id   datehour              type        count  

1   2020-09-23 16:00:00   device_a     3
2   2020-09-23 16:00:00   device_b     2
3   2020-09-23 16:00:00   device_all   5
4   2020-09-23 17:00:00   device_a     1
5   2020-09-23 17:00:00   device_b     2
6   2020-09-23 17:00:00   device_all   3

What is precise MySQL version? – Akina Nov 27 '20 at 04:36 — Akina, Nov 27 '20 at 04:36

Nick · Answer 1 · 2020-11-27T05:31:13.480

You first need to count the occurrences of each device for each hour period. Once you've done that, you can then CROSS JOIN that data with a list of device types, summing the occurrences when the device types match, or the device type is device_all:

SELECT ROW_NUMBER() OVER (ORDER BY datehour, FIND_IN_SET(type, 'device_a,device_b,device_all')) AS id,
       d.datehour,
       t.type,
       SUM(CASE WHEN d.device = t.type OR t.type = 'device_all' THEN `count` END) AS `count`
FROM (
  SELECT 'device_a' AS type
  UNION ALL
  SELECT 'device_b'
  UNION ALL 
  SELECT 'device_all'
) t
CROSS JOIN (
  SELECT datehour,
         device,
         COUNT(*) AS count
  FROM (
    SELECT 'device_a' AS device, `datetime` AS datehour
    FROM device_a
    UNION ALL
    SELECT 'device_b', `datetime`
    FROM device_b
  ) dv
  GROUP BY datehour, device
) d
GROUP BY datehour, type 
ORDER BY datehour, FIND_IN_SET(type, 'device_a,device_b,device_all')

Output:

id  datehour                type        count
1   2020-09-23 16:00:00     device_a    3
2   2020-09-23 16:00:00     device_b    2
3   2020-09-23 16:00:00     device_all  5
4   2020-09-23 17:00:00     device_a    1
5   2020-09-23 17:00:00     device_b    2
6   2020-09-23 17:00:00     device_all  3

Demo on db-fiddle

Note it seems something like this might be more useful:

SELECT datehour,
       SUM(device = 'A') AS device_a,
       SUM(device = 'B') AS device_b,
       COUNT(*) AS device_all
FROM (
  SELECT 'A' AS device, `datetime` AS datehour
  FROM device_a
  UNION ALL
  SELECT 'B', `datetime`
  FROM device_b
) d
GROUP BY datehour

Output:

datehour                device_a    device_b    device_all
2020-09-23 16:00:00     3           2           5
2020-09-23 17:00:00     1           2           3

Demo on db-fiddle

how about to add id (row number) based on order by date hour ? like my expected result @Nick — hinafaya, Nov 27 '20 at 05:20

score 0 · Answer 2 · answered Nov 27 '20 at 04:58

Try to use this one

SET @rownum=0;
SELECT @rownum:=@rownum+1 AS id, a.datetime, 'device_a' AS 'type',COUNT(*) AS 'count' 
FROM device_a a 
GROUP BY a.datetime
UNION
SELECT @rownum:=@rownum+1 AS id,b.datetime, 'device_b' AS 'type',COUNT(*) AS 'count' 
FROM device_b b
GROUP BY b.datetime
UNION   
SELECT @rownum:=@rownum+1 AS id, a.datetime, 'device_all' AS 'type',(COUNT(*) + (SELECT COUNT(*) FROM device_b WHERE datetime = a.datetime)) AS 'count' 
FROM device_a a 
GROUP BY a.datetime
ORDER BY `datetime` asc

result is enter image description here

Count data with different table

2 Answers2