0

I'm querying an Invoice table in a MySQL 8.0.28 database that is a child to the Customer table (FK = CustomerId) and I want to be able to get multiple counts based on different criteria over the last 30 days where I group by the customer and the invoice's source. So if I wrote it as 2 separate queries, it would be like this:

/* this is just the count of all invoices in last 30 days */
SELECT i.CustomerId, i.InvoiceSource, COUNT(*) AS TotalInvoices
FROM Invoice i
WHERE i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
GROUP BY i.CustomerId, i.InvoiceSource;

and

/* count of all invoices in last 30 days where the total amount exceeds 1000 */
SELECT i.CustomerId, i.InvoiceSource, COUNT(*) AS LargeInvoices
FROM Invoice i
WHERE i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH) and CURDATE() AND i.Amount > 1000
GROUP BY i.CustomerId, i.InvoiceSource;

You get the idea, there will probably be 2 or 3 more with specific criteria as well.

So my questions are:

  1. first and foremost what is the best approach and SQL to write this as a single query?
  2. will it start to bog down the query as I add more counts for different conditions? if so, what's the over/under on that?
  3. The GROUP BY is the same for all, am I able to move that out of each subquery and make it as a group by for the entire thing? Not sure if that would be more efficient or not?

much obliged

1 Answer 1

1

One way to do it is to use a CASE inside an aggregation function.

As an example, you could combine the two queries this way:

SELECT
    i.CustomerId,
    i.InvoiceSource,
    COUNT(*) AS TotalInvoices,
    SUM(CASE WHEN i.Amount >1000 then 1 ELSE 0 END ) as LargeInvoices
FROM
    Invoice i
WHERE
    i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH)
    and CURDATE()
GROUP BY
    i.CustomerId,
    i.InvoiceSource;

It could also be done with COUNT:

SELECT
    i.CustomerId,
    i.InvoiceSource,
    COUNT(*) AS TotalInvoices,
    COUNT(CASE WHEN i.Amount >1000 then 1 ELSE NULL END ) as LargeInvoices
FROM
    Invoice i
WHERE
    i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH)
    and CURDATE()
GROUP BY
    i.CustomerId,
    i.InvoiceSource;
2
  • Great, thanks, very simple and clean solution @Eric Prevost Commented Aug 25, 2023 at 11:11
  • Or... SUM(i.Amount >1000)
    – Rick James
    Commented Sep 15, 2023 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.