CODEWITHSUNDEEP
mysql
Alex _TNT
Alex _TNT

Reputation: 319

SUM table by DAYNAME and name while having time inteval

I have this table called "downloads" composed like this

id  date                name                                      quantity
 1  2015-05-13 23:30:09 CRCP03 - Cartoon Race Car Pack 03                1
 2  2015-05-14 00:30:09 CRCPV2-01 - Cartoon Race Cars Pack V2 - 01       1
 3  2015-05-14 17:10:06 CRCP03 - Cartoon Race Car Pack 03                1
 4  2015-05-15 23:47:07 CRCPV2-01 - Cartoon Race Cars Pack V2 - 01       1
 5  2015-05-16 03:31:06 CRCP03 - Cartoon Race Car Pack 03                1
 6  2015-05-17 13:13:06 CRCP03 - Cartoon Race Car Pack 03                1
 7  2015-05-18 00:32:06 CRCPV2-01 - Cartoon Race Cars Pack V2 - 01       1

I want to SUM all the "name" by DAYNAME while having an interval DATE_SUB(curdate(), INTERVAL 1 MONTH) . Also the sum to be name aware

Currenty what I have is this

SELECT name, SUM(quantity), DAYNAME(d.date) AS weekday 

    FROM downloads d
GROUP BY WEEKDAY(d.date)

/*// This does not work I get a fetch_assoc error also does not have a time interval
    SELECT WEEKDAY(d.date) AS weekday,
             SUM(CASE WHEN d.name = 'CRCP03 - Cartoon Race Car Pack 03' THEN 1 ELSE 0 END) AS a,
             SUM(CASE WHEN d.name = 'CRCPV2-01 - Cartoon Race Cars Pack V2 - 01' THEN 1 ELSE 0 END) AS b,
        FROM downloads d
    GROUP BY WEEKDAY(d.date) */

I get this kind of mixed result without interval

Monday - CRCP03 - Cartoon Race Car Pack 03 - 70
Tuesday - CRCPV2-01 - Cartoon Race Cars Pack V2 - 01 - 72
Wednesday - CRCP03 - Cartoon Race Car Pack 03 - 89
Thursday - CRCP03 - Cartoon Race Car Pack 03 - 77
Friday - CRCP03 - Cartoon Race Car Pack 03 - 70
Saturday - CRCP03 - Cartoon Race Car Pack 03 - 68
Sunday - CRCPV2-01 - Cartoon Race Cars Pack V2 - 01 - 49

Thank you for anyone carrying to help

Upvotes: 0

Views: 77

Answers (1)

AdamMc331
AdamMc331

Reputation: 16730

To get the number of times each name appears for each weekday, you can use the aggregation you already have, but group by both name and weekday:

SELECT DAYNAME(dateColumn) AS weekDay, name, SUM(quantity) AS totalCount
FROM downloads
GROUP BY DAYNAME(dateColumn), name;

To only select on an interval, you can just add a where clause. For example, if I wanted to do this only for the month of may:

SELECT DAYNAME(dateColumn) AS weekDay, name, SUM(quantity) AS totalCount
FROM downloads
WHERE dateColumn BETWEEN '2015-05-01' AND '2015-05-31'
GROUP BY DAYNAME(dateColumn), name;

If you want to see the names side by side, with each row for a weekday, you need to look into a pivot table, which is hard to do. However, if you know the row names, you can use conditional aggregation:

SELECT DAYNAME(dateColumn) AS weekday,
  SUM(name = 'CRCPV2-01 - Cartoon Race Cars Pack V2 - 01') AS 'CRCPV2-01 - Cartoon Race Cars Pack V2 - 01',
  SUM(name = 'CRCP03 - Cartoon Race Car Pack 03') AS 'CRCP03 - Cartoon Race Car Pack 03'
FROM downloads
WHERE dateColumn BETWEEN '2015-05-01' AND '2015-05-31'
GROUP BY DAYNAME(dateColumn);

Both queries can be seen in action at this SQL Fiddle.

Upvotes: 1

Related Questions