Link to home
Start Free TrialLog in
Avatar of dennis231
dennis231

asked on

Weekday order and additional calculation in MySQL

My query returns weekdays in alphabetical order likes following:

Friday
Monday
Saturday
Sunday
Thursday
Tuesday
Wednesday

Question #1:
How do I re-write this query and make the weekday shown in the order of:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Question #2:
How do I add a additional column in this query to show a calculated result?
e.g.
Weekday|Win|Lost|Rate(Win/Lost)
Monday|4|5|80%

Thanks.

SELECT date_format( GameDate, '%W' ) , SUM( Win ) , SUM( Lost )
FROM MyTable
WHERE GameDate >= date_sub( now( ) , INTERVAL 500
MONTH )
GROUP BY date_format( GameDate, '%W' )
ORDER BY date_format( GameDate, '%W' )

Open in new window

Avatar of Roger Baklund
Roger Baklund
Flag of Norway image
Try using %w (lower case) instead of %W in the ORDER BY clause:

...ORDER BY date_format( GameDate, '%w' )

This will order with sunday first, then monday, tuesday...

To put sunday last, try this:

...ORDER BY date_format( GameDate, '%w' )=0,date_format( GameDate, '%w' )
Avatar of Roger Baklund
Roger Baklund
Flag of Norway image
...oh, and the calculated column:

CONCAT(FLOOR((SUM( Win )/SUM( Lost ))*100),'%') AS 'Rate(Win/Lost)'
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial