MySQL 5.6 – Select Same Data Set for Different Date Ranges in Single Query

MySQLmysql-5.6

what I am trying to do is performing a query that has multiple results of the same query by just editing only a where clause.

The goal is to query a result of the same dataset in different date ranges, but instead of performing multiple separated queries, having one single query that queries the tables X times (depending on the selected date ranges).

For example: 

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total"
FROM database_two.announcements
INNER JOIN database_one.prospect_evaluations ON database_one.prospect_evaluations.announcement_id = database_two.announcements.id
INNER JOIN database_one.users ON database_one.prospect_evaluations.user_id = database_one.users.id
WHERE (database_two.announcements.deleted_at IS NULL
       AND (database_two.announcements.archived_at IS NOT NULL
            AND database_two.announcements.crmstate != 'Scartata'))
  AND (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) /* THIS IS THE DATE RANGE TO EDIT IN THE SUBQUERY */
GROUP BY database_one.prospect_evaluations.user_id
ORDER BY database_two.announcements.id DESC

What I'd like to have is something like this, in pseudo-sql:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       /* PSEUDO-SQL START { */
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) WHERE (database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
       /* } PSEUDO-SQL STOP */
FROM database_two.announcements
INNER JOIN database_one.prospect_evaluations ON database_one.prospect_evaluations.announcement_id = database_two.announcements.id
INNER JOIN database_one.users ON database_one.prospect_evaluations.user_id = database_one.users.id
WHERE (database_two.announcements.deleted_at IS NULL
       AND (database_two.announcements.archived_at IS NOT NULL
            AND database_two.announcements.crmstate != 'Scartata'))
GROUP BY database_one.prospect_evaluations.user_id
ORDER BY database_two.announcements.id DESC

Best Answer

Your pseudo-SQL is surprising close to the standard SQL clause FILTER, which has only been implemented by Postgres but unfortunately not by MySQL:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: FILTER
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
...

The good news is that in MySQL you can use equivalent (and standard SQL as well) CASE expressions:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: COUNT(CASE ...)
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
               THEN 1 END) AS "1 Month"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
               THEN 1 END) AS "3 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)
               THEN 1 END) AS "6 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
               THEN 1 END) AS "1 Year"
...

or the more compact but non-standard SUM() that uses the implicit conversions of boolean values (TRUE and FALSE) to 1 and 0:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
          ) AS "1 Month"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
          ) AS "3 Months"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)
          ) AS "6 Months"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
          ) AS "1 Year"
...

By the way, you could use aliases for the various tables in the FROM clauses so the query becomes shorter and usually more readable.

Also the announcements.id is ambiguous in the SELECT orr ORDER BY clause after a GROUP BY, because it is not in the group by list. Use MIN() or MAX() or some other ORDER BY instead.

The query with aliases - and assuming you choose the SUM() solution from above:

SELECT u.firstname,
       u.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR))  AS "1 Year"
FROM database_two.announcements  AS a
JOIN database_one.prospect_evaluations  AS pe
    ON pe.announcement_id = a.id
JOIN database_one.users  AS u
    ON pe.user_id = u.id
WHERE a.deleted_at IS NULL
  AND a.archived_at IS NOT NULL
  AND a.crmstate <> 'Scartata'
GROUP BY pe.user_id
ORDER BY MAX(a.id) DESC ;
Related Question