December 21, 2005 at 4:15 pm
ok i couldnt get that right in the subject but what i'm trying to do is query the same filed twice so that the returned results contain different data based on certain criteria
for example
in an ORDERS tables i have several orders submitted at different times by a customer.
I want to write a query (only one query) that will return total number of orders placed by the customer to date (this is simple enough by doing count()) and also to return number of orders placed till the end of last week (this is simple too by itself using the dateadd function)
so if a customer placed 20 orders till the end of last week and 2 more orders this week then the query should return 22 and 2
How do i do this in one query?
December 21, 2005 at 4:33 pm
COUNT(*) and SUM( CASE WHEN datefield BETWEEN whenever AND whenever THEN 1 ELSE 0 END )
December 21, 2005 at 7:05 pm
Select C.Name, COUNT(O1.OrderId), COUNT(O2.OrderId)
FROM Customer C
LEFT JOIN Order O1 on C.ID = O1.CustomerId and O1.OrderDate between ... and ...
LEFT JOIN Order O2 on C.ID = O2.CustomerId and O2.OrderDate between ... and ...
GROUP BY C.Name
_____________
Code for TallyGenerator
December 22, 2005 at 2:51 am
You will probably need COUNT(distinct O1.OrderId), COUNT(distinct O2.OrderId) to get that working.... I like John's solution, however:
select
CustomerId,
sum(case when OrderDate between @d1 and @d2 then 1 else 0 end),
sum(case when OrderDate between @d3 and @d4 then 1 else 0 end)
from Orders group by CustomerId
Alternatively,
select
CustomerId,
count(*),
sum(case when OrderDate between @d3 and @d4 then 1 else 0 end)
from Orders group by CustomerId
Or simply
select
CustomerId,
count(*),
sum(case when OrderDate between @d3 and @d4 then 1 else 0 end)
from Orders where CustomerId = @CustomerId
December 22, 2005 at 7:25 am
Thanks for the responses guys. I used Johns method and it works perfectly. I appreciate the help everyone.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply