Group by data based on day of week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by data based on day of week

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-24 : 14:34:11

Hi,
I am a .net developer , not expert in writing sql queries.
we are maitaining No of items on each Date and for different locations in table.

I want to show Total Items for DayOfweek(sunday,Monday etc) for each location

I have written query like this

declare @startdate datetime
declare @enddate datetime
set @startdate = '10/1/2011'
set @enddate = '3/31/2012'
SELECT
Sum(Items) as Totalitem, Location
FROM
ItemList
WHERE
1=1
and (ItemDate <= @enddate)
and (ItemDate >= @startdate)
Group By Location

for this i am getting output as

Totalitem Location

100 Texas

but i want like this

WeekDay TotalItems Location

Sunday 100 Texas

Monday 118 Houston

please please help me..

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:03:01

[code]
SELECT DATENAME(dw,ItemDate) AS DayOfWeek,
Sum(Items) as Totalitem, Location
FROM
ItemList
WHERE (ItemDate <= @enddate)
and (ItemDate >= @startdate)
Group By DATENAME(dw,ItemDate),Location
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-24 : 15:20:04

Thanks for reply..

But i am getting all values , i want to get sum of items based on weekday and location.. Like

On friday In Texas Location we need to show totalitems depending on startdate and enddate. similarly

On Satday In Texas Location we need to show totalitems depending on startdate and enddate.

Lime that we should get only 7 records..(Sunday to Satday)

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:37:42

quote:
Originally posted by archana23

Thanks for reply..

But i am getting all values , i want to get sum of items based on weekday and location.. Like

On friday In Texas Location we need to show totalitems depending on startdate and enddate. similarly

On Satday In Texas Location we need to show totalitems depending on startdate and enddate.

Lime that we should get only 7 records..(Sunday to Satday)

Archana

nope..thats wrong
it wont be 7 records as day will get repeat for each location
so depending on how many locations you've you will get number * 7 records which is exactly what you asked for as per your posted sample output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-24 : 15:40:09

yes .. now i am getting..

Thank you so much for your help..

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:43:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-24 : 16:07:43

One more addition qn please..

I want to calculate average by TotalItem/No.ofweekdays(howmany fridays/satday/monday etc on that duration)

Previous Output

WeekDay TotalItems Location

Sunday 100 Texas

Monday 118 Houston
If total sundays are 10 & Mondays are 9 then output should be
Weekday Avg
sunday 100/10
Monaday 118/9

Can you please help me on this..

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 16:17:58

[code]
SELECT DATENAME(dw,ItemDate) AS DayOfWeek,
Sum(Items) as Totalitem, Location ,
Sum(Items)*1.0/COUNT(*) AS [Avg]
FROM
ItemList
WHERE (ItemDate <= @enddate)
and (ItemDate >= @startdate)
Group By DATENAME(dw,ItemDate),Location
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-05-24 : 16:30:30

Thank you so much.. Appreciated..

Archana

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-05-25 : 03:06:40

[code]Sum(Items)*1.0/COUNT(*) AS [Avg] [/code]?
surely
[code]Avg(Items)[/code]
or at worst
[code]Avg(Items*1.0)[/code]

Am I missing something (it is late!)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources