Select count for various conditions from same table in one query

Question

Select count for various conditions from same table in one query

Ravjeet bedi

Newbie

Points: 8
More actions
June 10, 2006 at 9:49 am

#67549

The query I want is something like
- find number of counts group by Category_id and Type_id
- find total number of records where date is greater than 2/1/2006(Some date)
- find total number of records where date is less than 3/1/2006(some date)
- find total number of records
etc in a single query

If I do
Select count(*) as total,
(Select count(*) from tableone where date > '2/1/2006' and
Category_id = tab1. Category_id and Type_id = tab1. Type_id
) as total2,
(Select count(*) from tableone where date < '3/1/2006' and Category_id = tab1. Category_id and Type_id = tab1. Type_id) as total3
from tableone tab1
group by Category_id ,Type_id

then it displays the result but takes a lot of time in fetching it. can u provide me with a better solution
The output I want is something like this
Category_Id
Type_id
Total
Total2
Total3
1
        2
1
0
0
1
        3
1
1
1
2
        1
2
1
1
4
        5
1
1
1
5
        6
1
0
0

Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

To count sub-sets, use CASE to make your different tests. For each test, the CASE should return 1 for true, and 0 for false. Then you can just sum() the results, and the number of 'True' hits will be returned.

SELECT CategoryID, TypeID, COUNT(*) AS [Total],
       SUM(CASE WHEN RecordDate > @Date1 THEN 1 ELSE 0 END) AS [AfterDate1],
       SUM(CASE WHEN RecordDate < @Date2 THEN 1 ELSE 0 END) AS [BeforeDate2]
  FROM dbo.table1
 GROUP BY CategoryID, TypeID

-Eddie

Eddie Wuerch
MCM: SQL

Ravjeet bedi Newbie Points: 8 More actions · Answer 2

Thanks Eddie I guess this would solve my problem