Grouping and counting

Hi,
I will try to explain:
I want the proper grouping and display counts. This query
works fine, see below some of the returned results.
SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
FROM
TempE INNER JOIN TempD
ON TempE.Numb = TempD.Numb
WHERE(TempE.sp in('bm','pm') or
TempE.sp2 in('bm','pm'))
GROUP BY TempD.state,TempD.City,TempD.Zip
ORDER BY TempD.state, TempD.City
Some of the results returned:
State City Zip total
Alabama ALBASTER 35007 2
Alabama BIRMINGHAM 35292 1
Arizona YUMA 85364 1
California PALMDALE 93550 1
Connecticut NEW LONDON 06320 2
Connecticut WOODBRIDGE 06525 1
Delaware CLAYMONT 19703 1
Delaware WILMINGTON 19801 3
North Carolina ASHEVILLE 28815 10
North Carolina ASHEVILLE 28816 3
South Carolina CHAPIN 29036 1
South Carolina CHARLESTON 29401 116
South Carolina CHARLESTON 29402 8
I want two more columns after total for "bm" and "pm" and with
the totals broke down like the following
State City Zip total
bm pm
Alabama ALBASTER 35007 2 1
1
Alabama BIRMINGHAM 35292 1 1 0
Arizona YUMA 85364 1 0
1
California PALMDALE 93550 1 1
0
Connecticut NEW LONDON 06320 2 2 0
Connecticut WOODBRIDGE 06525 1 0 1
Delaware CLAYMONT 19703 1 1 0
Delaware WILMINGTON 19801 3 3 0
North Carolina ASHEVILLE 28815 10 7 3
North Carolina ASHEVILLE 28816 3 0 3
South Carolina CHAPIN 29036 1 1
0
South Carolina CHARLESTON 29401 116 86 30
South Carolina CHARLESTON 29402 8 2 6
hope I was clear, Thanks
gvTry using a "case" expression.
SELECT
TempD.state,
TempD.City,
TempD.Zip,
count(TempE.ID)AS total,
sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as bm,
sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as pm
from
..
AMB
"gv" wrote:

> Hi,
> I will try to explain:
> I want the proper grouping and display counts. This query
> works fine, see below some of the returned results.
> SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
> FROM
> TempE INNER JOIN TempD
> ON TempE.Numb = TempD.Numb
> WHERE(TempE.sp in('bm','pm') or
> TempE.sp2 in('bm','pm'))
> GROUP BY TempD.state,TempD.City,TempD.Zip
> ORDER BY TempD.state, TempD.City
> Some of the results returned:
> State City Zip total
> Alabama ALBASTER 35007 2
> Alabama BIRMINGHAM 35292 1
> Arizona YUMA 85364 1
> California PALMDALE 93550 1
> Connecticut NEW LONDON 06320 2
> Connecticut WOODBRIDGE 06525 1
> Delaware CLAYMONT 19703 1
> Delaware WILMINGTON 19801 3
> North Carolina ASHEVILLE 28815 10
> North Carolina ASHEVILLE 28816 3
> South Carolina CHAPIN 29036 1
> South Carolina CHARLESTON 29401 116
> South Carolina CHARLESTON 29402 8
> I want two more columns after total for "bm" and "pm" and with
> the totals broke down like the following
> State City Zip total
> bm pm
> Alabama ALBASTER 35007 2 1
> 1
> Alabama BIRMINGHAM 35292 1 1 0
> Arizona YUMA 85364 1
0
> 1
> California PALMDALE 93550 1 1
> 0
> Connecticut NEW LONDON 06320 2 2 0
> Connecticut WOODBRIDGE 06525 1 0 1
> Delaware CLAYMONT 19703 1 1
0
> Delaware WILMINGTON 19801 3 3 0
> North Carolina ASHEVILLE 28815 10 7 3
> North Carolina ASHEVILLE 28816 3 0
3
> South Carolina CHAPIN 29036 1 1
> 0
> South Carolina CHARLESTON 29401 116 86 30
> South Carolina CHARLESTON 29402 8 2 6
> hope I was clear, Thanks
> gv
>
>
>|||Thanks!!
gv
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1996E862-0C8C-4C22-A154-57D5B728A650@.microsoft.com...
> Try using a "case" expression.
> SELECT
> TempD.state,
> TempD.City,
> TempD.Zip,
> count(TempE.ID)AS total,
> sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as
> bm,
> sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as
> pm
> from
> ...
>
> AMB
> "gv" wrote:
>