SQL Query problem. - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query problem.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-14 : 08:57:33

vick writes "I have query which returns

select [dayname], [hour], count([hour]) as nooforders from (
select
datepart(hour, order_date) as [hour],
datename(WEEKDAY, order_date) as [dayname]
from [order]
where order_date between '4/03/2006' and '4/05/2006'
) tempCount
group by [dayname],[hour]
order by [dayname],[hour]

day hr nooforder
Monday 1 1
Monday 18 76
Monday 19 72
Monday 20 52
Monday 21 40
Monday 22 24
Monday 23 21
Tuesday 0 8
Tuesday 1 3
Tuesday 3 6
Tuesday 4 9
Tuesday 5 7
Tuesday 6 27

I would like to also print the subtotal of each day in a query how I can do it? means Monday total order is x like that

I would appreciate your reply

Thanks
Vick"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-14 : 09:03:37

Do subtotals in the Front-End or use compute by clause !

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-17 : 07:41:10

Modify your query like this...

select [dayname], [hour], count([hour]) as nooforders from (
select 
datepart(hour, order_date) as [hour],
datename(WEEKDAY, order_date) as [dayname]
from [order]
where order_date between '4/03/2006' and '4/05/2006' 
) tempCount 
group by [dayname],[hour]
order by [dayname],[hour]
with rollup
having grouping([dayname]) = 0
order by [dayname], grouping([hour]), [hour]

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources