MySQL - Count all rows for each day of week but for each sender

debugcn Published at Dev

AdRock

I have this query which outputs the count of rows for each day and if there are no rows, it outputs 0.

I now have an additional field I want to add into the query called 'Sender'. I need the query to do exactly the same but for each of the senders.

How can I perform a query so each Sender gets each day of the week with the values?

SELECT DAYNAME(DATE_SUB(CURDATE(), INTERVAL Days.n DAY)) AS `day`,
       COUNT(r.List_Date) AS `total`
FROM (SELECT 1 as n UNION ALL SELECT 2 as n UNION ALL 
      SELECT 3 as n UNION ALL SELECT 4 as n UNION ALL
      SELECT 5 as n UNION ALL SELECT 6 as n UNION ALL
      SELECT 7 as n
     ) Days LEFT JOIN
     returns r
     ON r.List_Date >= DATE_SUB(CURDATE(), INTERVAL Days.n DAY)
GROUP BY Days.n
ORDER BY Days.n DESC

Gordon Linoff

You need a cross join to get all the rows (each sender and each day of the week). Then use the left join:

SELECT s.sender, DAYNAME(DATE_SUB(CURDATE(), INTERVAL Days.n DAY)) AS `day`,
       COUNT(r.List_Date) AS `total`
FROM (SELECT 1 as n UNION ALL SELECT 2 as n UNION ALL 
      SELECT 3 as n UNION ALL SELECT 4 as n UNION ALL
      SELECT 5 as n UNION ALL SELECT 6 as n UNION ALL
      SELECT 7 as n
     ) Days CROSS JOIN
     (SELECT DISTINCT sender FROM returns) s LEFT JOIN
     returns r
     ON r.List_Date >= DATE_SUB(CURDATE(), INTERVAL Days.n DAY) and
        r.sender = s.sender
GROUP BY s.sender, Days.n
ORDER BY s.sender, Days.n DESC;

This uses the returns table to get the appropriate senders. If you have another table, you can use that instead.

Collected from the Internet

Please contact [email protected] to delete if infringement.