How to combine multiple counts from different tables in one mysql query?

Question

I have a few different queries for getting some counters. Is it possible to combine these queries to one, but not to sum&union the counters?

For example:

SELECT
  COUNT(uuid) AS total_admins ta,
  SUM(CASE WHEN isPublished = 1 THEN 1 ELSE 0 END) AS total_admins_published tap,
  SUM(CASE WHEN isPublished = 0 THEN 1 ELSE 0 END) AS total_admins_unpublished tau
FROM admins

and

SELECT
  COUNT(uuid) AS total_mediators tm,
  SUM(CASE WHEN isPublished = 1 THEN 1 ELSE 0 END) AS total_mediators_published tmp,
  SUM(CASE WHEN isPublished = 0 THEN 1 ELSE 0 END) AS total_mediators_unpublished tmu
FROM mediator

and

SELECT
  COUNT(uuid) AS total_posts tp,
  SUM(CASE WHEN submissionDate BETWEEN "start" AND "end" THEN 1 ELSE 0 END) AS total_posts_week tpw,
  SUM(CASE WHEN submissionDate BETWEEN "start" AND "end" THEN 1 ELSE 0 END) AS total_posts_month tpm,
  SUM(CASE WHEN submissionDate BETWEEN "start" AND "end" THEN 1 ELSE 0 END) AS total_posts_year tpy
FROM posts

And what I expect:

| ta | tap | tau | tm | tmp | tmu | tp  | tpw | tpm | tpy |
|----|-----|-----|----|-----|-----|-----|-----|-----|-----|
| 20 | 10  | 10  | 12 | 10  | 2   | 230 | 30  | 180 | 220 |

Nick · Accepted Answer · 2020-05-07 08:33:43Z

You could just CROSS JOIN the three queries:

SELECT ta, tap, tau, tm, tmp, tmu, tp, tpw, tpm, tpy
FROM (
    SELECT
      COUNT(uuid) AS total_admins ta,
      SUM(CASE WHEN isPublished = 1 THEN 1 ELSE 0 END) AS total_admins_published tap,
      SUM(CASE WHEN isPublished = 0 THEN 1 ELSE 0 END) AS total_admins_unpublished tau
    FROM admins
) a
CROSS JOIN (
    SELECT
      COUNT(uuid) AS total_mediators tm,
      SUM(CASE WHEN isPublished = 1 THEN 1 ELSE 0 END) AS total_mediators_published tmp,
      SUM(CASE WHEN isPublished = 0 THEN 1 ELSE 0 END) AS total_mediators_unpublished tmu
    FROM mediator
) m
CROSS JOIN (
    SELECT
      COUNT(uuid) AS total_posts tp,
      SUM(CASE WHEN submissionDate BETWEEN "start" AND "end" THEN 1 ELSE 0 END) AS total_posts_week tpw,
      SUM(CASE WHEN submissionDate BETWEEN "start" AND "end" THEN 1 ELSE 0 END) AS total_posts_month tpm,
      SUM(CASE WHEN submissionDate BETWEEN "start" AND "end" THEN 1 ELSE 0 END) AS total_posts_year tpy
    FROM posts
) p

Collectives™ on Stack Overflow

How to combine multiple counts from different tables in one mysql query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related