Posted by M A Srinivas on 03/14/07 05:35

On Mar 14, 8:04 am, "Spook" <S...@mailinator.com> wrote:
> I have a SQL table with the following fields:
>
> accounts, orderid's and datetime
>
> Account OrderID Datetime
> 1 1 2007-03-01 09:30
> 1 2 2007-03-01 09:35
> 10 3 2007-03-01 10:30
> 2 4 2007-03-01 11:30
> 10 5 2007-03-01 12:30
>
> Using Query Analyzer, I'd like to run a query where the results are a count
> of orderId's by account on any given day like what I have below:
>
> Account Orders Date
> 1 2 2007-03-01
> 2 1 2007-03-01
> 10 2 2007-03-01
>
> Eventually getting it to this output:
>
> Date TotalOrder
> 2007-03-01 5
>
> Thanks for the help!

Try this

declare @tbla table (account int,orderid int, record_date datetime)
insert into @tbla values (1,1,'2007-03-01 09:30')
insert into @tbla values (1,2,'2007-03-01 09:35')
insert into @tbla values (10,3,'2007-03-01 10:30')
insert into @tbla values (2,4,'2007-03-01 11:30')
insert into @tbla values (10,5,'2007-03-01 12:30')

select
T. from (
select account,convert(varchar(10),record_date,101) as record_date,
count(*) as NoofOrders
from @tbla
group by
account,
convert(varchar(10),record_date,101)
with cube ) T
where
(
(T.account is not null and T.record_date is not null )
OR
(T.account is null and T.record_date is null )
)

M A Srinivas

[Back to original message]