[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

SQL statement to query data based on month and weekday

My  table has some basketball data with following format:

GameDate|Win|Lost
2005-05-16 00:00:00|1|0
2005-05-17 00:00:00|0|1
2005-05-18 00:00:00|1|0

My goal is to create 2 SQL statements which can show me the summary based on month and weekday:

Summary #1 Example:
(based on month, Order by January,February,March and so on)
(Date Range, all dates in the current table)
Month|Win|Lost
======================
January|11|12
February|11|12
March|11|12
April|12|14
May|16|12
June|3|13
July|13|4
August|11|12
September|11|12
October|11|12
November|22|14
December|31|11


Summary #2 Example:
(based on weekday, Order by Monday and Tuesday and so on)
(Date Range, all dates in the current table)
Weekday|Win|Lost
======================
Monday|7|12
Tuesday|3|12
Wednesday|11|12
Thursday|23|12
Friday|7|16
Saturday|17|13
Sunday|13|18

I have been learning some sql statement like the one listed below, but I have no clue to figure out how to modify it to apply to my case.

All I need is two SQL statements, Thanks for your help.

SELECT date_format( GameDate, '%M' ) , count( *)
FROM tblWangGame
WHERE GameDate >= date_sub( now( ) , INTERVAL 99
MONTH )
GROUP BY date_format( GameDate, '%M' )
ORDER BY date_format( GameDate, '%M' )

Open in new window

0
dennis231
Asked:
dennis231
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simply:
select datename(month, GameDate), sum(Win), sum(Lost)
from yourtable 
group by datename(month, GameDate) 

select datename(weekday, GameDate), sum(Win), sum(Lost)
from yourtable 
group by datename(weekday, GameDate)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and for the order by to work
select datename(month, GameDate), sum(Win), sum(Lost)
from yourtable 
group by datename(month, GameDate) , datepart(month, GameDate)
order by datepart(month, GameDate) 

select datename(weekday, GameDate), sum(Win), sum(Lost)
from yourtable 
group by datename(weekday, GameDate) , datepart(weekday, GameDate)
order by datepart(weekday, GameDate)

Open in new window

0
 
dennis231Author Commented:
I have tested it under phpMyAdmin , but I got following error:

MySQL version: 4.1.22

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(weekday, GameDate), sum(Win), sum(Lost)
from tblMyGame
group by datename(week' at line 1


I changed yourtable to myTableName, the GameDate in the field name I used in my table. What could be the problem? Thanks.
0
 
dennis231Author Commented:
Thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error makes it clear that you use MySQL.
the syntax will be a bit different, as the functions differ a bit.

do you need help to adapt?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now