MySQL: Multiple Aggregate SUM Criteria On a Single Join

22 May 2021

Below is an example of two aggregate SUM statements on a single join with different criteria. One gives the total downloads count of an user on all days, the other filters the same sum on past 30 days.

SELECT Sum(qbldownloads.count) AS downloads,
       Sum(IF(qbldownloads.date >= CURRENT_DATE - INTERVAL 30 day,
           qbldownloads.count,
           0)) AS downloads30,

Full query from an OpenCart project:

SELECT *,
       Concat(c.firstname, ' ', c.lastname) AS name,
       cgd.name                             AS customer_group,
       Sum(qbldownloads.count)              AS downloads,
       Sum(IF(qbldownloads.date >= CURRENT_DATE - INTERVAL 30 day,
           qbldownloads.count,
           0))                              AS downloads30,
       c.*
FROM   customer c
LEFT JOIN customer_group_description cgd
      ON ( c.customer_group_id = cgd.customer_group_id )
LEFT JOIN customer_2d_downloads qbldownloads
      ON ( qbldownloads.customer_id = c.customer_id )
WHERE  cgd.language_id = 1
GROUP  BY c.customer_id
ORDER  BY name ASC
LIMIT  0, 20