CODEWITHSUNDEEP
sqlmysql
Pjn2020
Pjn2020

Reputation: 537

MySql Count Same Column multiple times

SELECT COUNT(w.invoiceId) AS '10 Days' FROM tbl_Invoice w
WHERE w.invoiceId NOT IN(
SELECT inv.invoiceId FROM tbl_InvoiceAllocation inv)
AND w.invoiceDate < DATE_SUB(curdate(), INTERVAL 10 DAY)

It works fine and returns a count of all the invoices that have a date of more than 10 days ago. I now want to return counts for invoices that are also more than 20 and 100 days old in the same query. So ideally the query would return something like this:

10 Days    20 Days    100 Days

350        280        90

Upvotes: 3

Views: 1175

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166356

Change the COUNT to a SUM, using the specified WHERE clause as a CASE statement, with true values as 1 and false values as 0

Something like

SELECT  SUM( CASE WHEN w.invoiceDate < DATE_SUB(curdate(), INTERVAL 10 DAY) THEN 1 ELSE 0 END) AS '10 Days',
        SUM( CASE WHEN w.invoiceDate < DATE_SUB(curdate(), INTERVAL 20 DAY) THEN 1 ELSE 0 END) AS '20 Days'
FROM    tbl_Invoice w 
WHERE   w.invoiceId NOT IN( 
                            SELECT  inv.invoiceId 
                            FROM    tbl_InvoiceAllocation inv) 
AND     w.invoiceDate < DATE_SUB(curdate(), INTERVAL 20 DAY)

Upvotes: 5

Related Questions