CODEWITHSUNDEEP
phpmysqlsql
hamburger
hamburger

Reputation: 1435

SQL Order by current day

I have a table like this an would like to order it beginning with the current dayname.

title   date_firstcall      totalViews  Tag
name A  2014-08-25 10:19:49     2   Monday
name B  2014-08-24 16:03:36     3   Sunday
name C  2014-08-24 14:54:47     2   Sunday
name C  2014-08-23 11:06:19     3   Saturday
name A  2014-08-23 00:05:35     16  Saturday
name B  2014-08-22 10:05:53     4   Friday
name A  2014-08-22 00:11:28     25  Friday
name C  2014-08-21 19:28:54     1   Thursday
name A  2014-08-21 08:44:05     13  Thursday
name C  2014-08-20 22:42:49     1   Wednesday
name D  2014-08-19 16:22:42     2   Tuesday
name A  2014-08-19 15:43:57     10  Tuesday
name B  2014-08-19 09:36:52     1   Tuesday

my not working SQL-statement:

SELECT title as Seite, DAYNAME(CURRENT_DATE) AS diff,
 sum( if( Tag = 'Monday', totalViews, 0 ) ) AS Montag,
 sum( if( Tag = 'Wednesday', totalViews, 0 ) ) AS Mittwoch,
 sum( if( Tag = 'Tuesday', totalViews, 0 ) ) AS Dienstag,
 sum( if( Tag = 'Thursday', totalViews, 0 ) ) AS Donnerstag,
 sum( if( Tag = 'Friday', totalViews, 0 ) ) AS Freitag,
 sum( if( Tag = 'Saturday', totalViews, 0 ) ) AS Samstag,
 sum( if( Tag = 'Sunday', totalViews, 0 ) ) AS Sonntag
 FROM tmp  GROUP BY title ORDER BY diff

the result table shout start with the current day at first followed by the next day. Like: 

        Seite   current day     next day    Dienstag    Mittwoch Donnerstag     Freitag     Samstag
       name A        2            7           0           3        9               2            2
       name c        3            2           1           0        9               2            2

Thx for any hint in advance.

Upvotes: 0

Views: 99

Answers (1)

Barmar
Barmar

Reputation: 781120

Use WEEKDAY to get the day of week as a number. Subtract the current day modulo 7, so it will wrap around, and then pivot this.

SELECT title,
       SUM(IF(WEEKDAY(date_firstcall) = WEEKDAY(NOW()), totalViews, 0)) AS current_day,
       SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 1, totalViews, 0)) AS day_plus_1,
       SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 2, totalViews, 0)) AS day_plus_2,
       SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 3, totalViews, 0)) AS day_plus_3,
       SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 4, totalViews, 0)) AS day_plus_4,
       SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 5, totalViews, 0)) AS day_plus_5,
       SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 6, totalViews, 0)) AS day_plus_6
FROM tmp
GROUP BY title

DEMO

Here's how to use dynamic SQL to get the day names:

SET @sql = (SELECT CONCAT('
     SELECT title,
            SUM(IF(WEEKDAY(date_firstcall) = WEEKDAY(NOW()), totalViews, 0)) AS ', DAYNAME(NOW()),
    ',      SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 1, totalViews, 0)) AS ', DAYNAME(DATE_ADD(NOW(), INTERVAL 1 DAY)),
    ',      SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 2, totalViews, 0)) AS ', DAYNAME(DATE_ADD(NOW(), INTERVAL 2 DAY)),
    ',      SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 3, totalViews, 0)) AS ', DAYNAME(DATE_ADD(NOW(), INTERVAL 3 DAY)),
    ',      SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 4, totalViews, 0)) AS ', DAYNAME(DATE_ADD(NOW(), INTERVAL 4 DAY)),
    ',      SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 5, totalViews, 0)) AS ', DAYNAME(DATE_ADD(NOW(), INTERVAL 5 DAY)),
    ',      SUM(IF((WEEKDAY(date_firstcall)-WEEKDAY(NOW()) % 7) = 6, totalViews, 0)) AS ', DAYNAME(DATE_ADD(NOW(), INTERVAL 6 DAY)),
    '       FROM tmp
     GROUP BY title'));
PREPARE stmt FROM @sql;
EXECUTE stmt;

DEMO

Upvotes: 1

Related Questions