?combine detailed and count in One Query?

[DLu]

Is it possible to combine a detailed query with its related count and sum without using any #temp tables at all?

ex. select customerID, customerName,
(select count(orderID) from tblOrder where orderDate > '01/01/2003'
and orderStatus = 'active') as countActive,
(select count(orderID) from tblOrder where orderDate > '01/01/2003'
and orderStatus = 'inactive') as countInActive
from rfCustomers

something like that? I was heard SQL2k has some new feature like this, or a UDF may be required? Currently, I have to use #temp table to get it.

thanks
David

[MAK]

Simple group by will do this.

create table tblOrder (orderId int, Orderstatus varchar(10), orderdate datetime,customerId int)
create table rfcustomer (customerId int, customername varchar(100))

insert into tblorder select 1,'active','8/1/2003',1
insert into tblorder select 2,'inactive','8/1/2003',1
insert into tblorder select 3,'active','8/1/2003',2
insert into tblorder select 4,'inactive','8/1/2003',2
insert into tblorder select 5,'inactive','8/1/2003',1
insert into tblorder select 6,'active','8/2/2003',1
insert into tblorder select 7,'inactive','8/3/2003',1
insert into tblorder select 8,'active','8/1/3003',2
insert into tblorder select 9,'inactive','8/3/2003',2
insert into tblorder select 15,'inactive','8/4/2003',1
insert into tblorder select 52,'inactive','8/1/2003',1

insert into rfcustomer select 1,'MAK'
insert into rfcustomer select 2,'Claire'


select rfcustomer.customerId,
rfcustomer.CustomerName,
count(tblorder.OrderId) as Count,
tblOrder.OrderStatus
from rfcustomer , (select * from tblorder where tblorder.orderdate > '1/1/03') as tblorder
where rfcustomer.customerID = tblorder.CustomerId
group by rfcustomer.customerId,
rfcustomer.CustomerName,
tblOrder.OrderStatus

[DLu]

Again, thanks MAX.
-D

[DLu]

Mak:
Based on your solution, I modified to put the group by with the (select customerID, count(*) as counting....) instead of at the end of the query.

this way, I could just add a virtual total table as I wish and finally to link them toghther.

thanks
-D

[MAK]

Thanks for the feedback. I am glad it worked