One query finds the aggregates the subtotals by country:

SELECT
    customer.country,
    SUM(i.subtotal) AS total
FROM
    invoices i
    LEFT JOIN customer ON i.customer_id = customer.id
WHERE
    status = 'Paid'
    AND datepaid BETWEEN '2020-05-01 00:00:00' AND '2020-06-01 00:00:00'
    AND customer.billing_day <> 0
    AND customer.register_date < '2020-06-01 00:00:00'
    AND customer.account_exempt = 'f'
    customer.country <> ''
GROUP BY
    customer.country;

The other query aggregates the subtotals for US customers by state:

SELECT
    customer.state,
    SUM(i.subtotal) AS total
FROM
    invoices i
    LEFT JOIN customer ON i.customer_id = customer.id
WHERE
    status = 'Paid'
    AND datepaid BETWEEN '2020-05-01 00:00:00' AND '2020-06-01 00:00:00'
    AND customer.billing_day <> 0
    AND customer.register_date < '2020-06-01 00:00:00'
    AND customer.account_exempt = 'f'
    AND customer.country = 'US'
    AND customer.state <> ''
GROUP BY
    customer.state;

Is it possible to write one query that returns each country's total, and if the country is the US, also returns the state's total? I have read that subqueries can be used to combine two aggregate functions, but I'm not sure how that would be done here.

CodePudding user response：

You can use two keys in the group by:

SELECT c.country,
       (CASE WHEN c.country = 'US' THEN c.state END) as state,
       SUM(i.subtotal) AS total
FROM invoices i JOIN
     customer c
     ON i.customer_id = c.id
WHERE i.status = 'Paid' AND
      i.datepaid >= '2020-05-01' AND
      i.datepaid < '2020-06-01' AND
      c.billing_day <> 0 AND
      c.register_date < '2020-06-01' AND
      c.account_exempt = 'f'
      c.country <> ''
GROUP BY c.country, (CASE WHEN c.country = 'US' THEN c.state END);

Note the other changes to the query:

• Your WHERE clause turns the outer join into an inner join, so LEFT JOIN is misleading.
• All column references are qualified.
• Presumably, you want dates in May, so I adjusted the logic.
• There is no need to include times when working only with dates.