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天的发票的计数。我现在想要返回在同一查询中同样存在20天以上和100天的发票的计数。理想情况下,查询会返回如下内容:
10 Days 20 Days 100 Days
350 280 90
1 个解决方案
#1
5
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
将计数更改为SUM,使用指定的WHERE子句作为CASE语句,以true值为1,false值为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)
#1
5
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
将计数更改为SUM,使用指定的WHERE子句作为CASE语句,以true值为1,false值为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)