March 8, 2005 at 9:03 am
I have a procedure that searches 25 Million records to pull out certain records.
I then Group these records based on a "Type" field.
Select Type, Count(Type)
From <myTable>
Where <criteria>
Group By Type
I end up with the following dataset:
Type Count
---- -----
1 100
2 400
3 600
Now, I want to set the following variable to their corresponding Counts: @Type1cnt, @Type2cnt, @Type3cnt
Is there a way to do this without running the query 3 times, once for each type?
March 8, 2005 at 9:12 am
Turn the existing query into a derived table and query/pivot from it into the variables:
Select
@Type1cnt = Sum(Case Type When 1 Then TypeCount Else 0 End),
@Type2cnt = Sum(Case Type When 2 Then TypeCount Else 0 End),
@Type3cnt = Sum(Case Type When 3 Then TypeCount Else 0 End)
From
(
Select Type, Count(Type) As TypeCount
From <myTable>
Where <criteria>
Group By Type
) dtTypeCount
Alternatively, since the result set is small, select it into a #temp table or @Table variable, and select each row from the resulting small result table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply