MySQL: Combine 3 queries into one

Question

I have 3 queries that collect statistic:

SELECT
  COUNT(id) count,
  DATE(created_at) date
FROM t1
GROUP BY
  date;

SELECT
  COUNT(id) count,
  DATE(created_at) date
FROM t2
GROUP BY
  date;

SELECT
  COUNT(id) count,
  DATE(date_started) date
FROM t1
GROUP BY
  date;

Right now I run these queries 3 separate times to get those data. Is it possible to combine these into 1 query to get something like this?

date       | count1 | count2 | count3
-------------------------------------
2018-04-25 | 5      | (null) | 2
2018-04-24 | (null) | 3      | 4

you can do it with the use of union to consolidate data and then pivot them from vertically to horizontly — Ankit Agrawal, Apr 25 '18 at 08:47

Sergey Menshov · Accepted Answer · 2018-04-25T09:09:41.203

3

Try the following

SELECT
  `date`,
  SUM(count1) count1,
  SUM(count2) count2,
  SUM(count3) count3
FROM
  (
    SELECT
      DATE(created_at) `date`,
      COUNT(id) count1,
      NULL count2,
      NULL count3
    FROM t1
    GROUP BY DATE(created_at)

    UNION ALL

    SELECT
      DATE(created_at) `date`,
      NULL count1,
      COUNT(id) count2,
      NULL count3
    FROM t2
    GROUP BY DATE(created_at)

    UNION ALL

    SELECT
      DATE(date_started) `date`,
      NULL count1,
      NULL count2,
      COUNT(id) count3
    FROM t1
    GROUP BY DATE(date_started)
  ) q
GROUP BY `date`

SQL Fiddle - http://www.sqlfiddle.com/#!9/bcfb9e/1

edited Apr 25 '18 at 09:09

answered Apr 25 '18 at 08:49

Sergey Menshov

3,856
2
8
19

I tried this and `count1` has numbers while the other `count2`,`count3` all have null value... – NaGeL182 Apr 25 '18 at 09:02
Sorry, the order of the selects wasn't in the same order as yours, and I thought the order isn't important after I changed it as you have it works. Seems like select order IS important. – NaGeL182 Apr 25 '18 at 09:10
OK. I've changed my answer and added SQL Fiddle test. – Sergey Menshov Apr 25 '18 at 09:11
Yes, the order of subqueries' columns is important when you use `UNION ALL`. But the order of subqueries isn't important. – Sergey Menshov Apr 25 '18 at 09:14

score 0 · Answer 2 · answered Apr 25 '18 at 08:47

You can do this with joins.
Just take the date as join column and use an outer join in order to get the null values where no combinations are found. Then rename the colums in the select part.

If you have problems with the group, you can use subquerys in MySQL as well.

You can also use the as keyword in order to have a better overview with your tables.

Of course it would be great to optimize your query when there are many tuples in the tables.

MySQL: Combine 3 queries into one

2 Answers2