We have three tables pictures , movies , stories . Each table has the same status columns, created_at .

status can be of three types: active , pending , deleted

It is necessary to receive in one request the number of lines created in the last hour and in the last 24 hours . Clearly, it should look something like this:

 ---------------------------------------------------- |Status |Pics1h|Pics24h|Mov1h|Mov24h|Stor1h|Stor24h| ---------------------------------------------------- |active | 12 | 30 | 8 | 14 | 20 | 31 | ---------------------------------------------------- |pending| 2 | 4 | 1 | 4 | 2 | 8 | ---------------------------------------------------- |deleted| 10 | 34 | 2 | 45 | 33 | 45 | ----------------------------------------------------

I can display one column, for example:

 SELECT status, count(*) as `Pics1h` FROM pictures WHERE (created_at >= DATE_SUB(NOW(), INTERVAL 60 MINUTE)) GROUP BY status;

How to get data from three tables, possibly with several queries, but as efficiently as possible?

  • And subqueries in request are not considered for a little? Otherwise, nothing at all - Alexey Shimansky
  • possible subqueries. the main thing to get the desired result, with minimal loads - Petr
  • Just a reminder (possibly useful for @Petr): COUNT(*) and COUNT(field) are not the same thing. - root_x Povierennyy

1 answer 1

 select status, sum(Pics1h) Pics1h, sum(Pics24h) Pics24h, sum(Mov1h) Mov1h, sum(Mov24h) Mov24h, sum(Stor1h) Stor1h, sum(Stor24h) Stor24h from ( SELECT status, sum(created_at >= NOW()-INTERVAL 60 MINUTE) Pics1h, count(1) Pics24h, NULL Mov1h, NULL Mov24h, NULL Stor1h, NULL Stor24h FROM pictures WHERE created_at >= NOW()-INTERVAL 1 DAY GROUP BY status UNION ALL SELECT status, NULL, NULL, sum(created_at >= NOW()-INTERVAL 60 MINUTE), count(1), NULL, NULL FROM Mov WHERE created_at >= NOW()-INTERVAL 1 DAY GROUP BY status UNION ALL SELECT status, NULL, NULL, NULL, NULL, sum(created_at >= NOW()-INTERVAL 60 MINUTE), count(1) FROM Stor WHERE created_at >= NOW()-INTERVAL 1 DAY GROUP BY status UNION ALL SELECT 'active', 0,0,0,0,0,0 UNION ALL SELECT 'pending', 0,0,0,0,0,0 UNION ALL SELECT 'deleted', 0,0,0,0,0,0 ) A GROUP BY status

UPD The last three UNIONs so that all the lines are exactly the result. Zeros can be replaced by NULL, depending on the desired result.

  • Everything works fine, except for options when, for example, there is no pending status in an hour or 24 hours. In this case, the string is not formed at all. Only two active and deleted - Petr
  • @Petr So this should be explicitly stated in the question that the lines should be ... - Mike