RE: Using COUNT() with CASE
From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 12/21/04
- Next message: Questar: "Re: ORDER BY and IDENTITY"
- Previous message: tarheels4025: "RE: Follow Up to Date Query Question"
- In reply to: DC Gringo: "Using COUNT() with CASE"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Dec 2004 07:45:02 -0800
> Count(CASE WHEN
> MineRiskEd = '<STRONG>Yes</STRONG>'
> THEN 1
> Else 0
> End
> As MineRiskEd)
The error is because you included the column alias inside the COUNT
aggregate function. Also, using COUNT in this way is equivalent to use
COUNT(*). If you want to count specifics one then do not include the ELSE
part or return NULL when the criteria is not meet.
Example:
select
count(all case when colA = 1 then 1 end)
from
(
select 1
union all
select 2
) as t(colA)
select
count(case when colA = 1 then 1 else 0 end)
from
(
select 1
union all
select 2
union all
select null
) as t(colA)
The first will return 1 and the second will return 3.
AMB
"DC Gringo" wrote:
> I getting an error: "Server: Msg 195, Level 15, State 10, Line 14 'Count' is
> not a recognized function name."
>
> How else can I correctly use the COUNT aggregate around this CASE statement:
>
>
>
> SELECT distinct clnGUID = '',
> Community = '',
> Impact = '',
> Round(Avg(PopulationKeyInfo),1) as PopulationKeyInfo,
> Avg(RecentVictims) as RecentVictims,
> Avg(NonRecentVictims) as NonRecentVictims,
> Avg(SHACount) as SHACount,
> MinDistance = '',
> Count(CASE WHEN
> MineRiskEd = '<STRONG>Yes</STRONG>'
> THEN 1
> Else 0
> End
> As MineRiskEd),
>
> VictimAssist = '',
> MarkingClearing = '',
> clnSubDistrictGUID = '',
> clnDistrictGUID = '',
> MineClearance = ''
>
> FROM vwCommunities
> WHERE EXISTS (
> SELECT *
> FROM vwCommunities v
> WHERE ( v.Impact IN ('High'))
> AND ( v.PopulationKeyInfo >= 3000 )
> AND clnGUID = vwCommunities.clnGUID)
>
>
> --
> _____
> DC G
>
>
>
- Next message: Questar: "Re: ORDER BY and IDENTITY"
- Previous message: tarheels4025: "RE: Follow Up to Date Query Question"
- In reply to: DC Gringo: "Using COUNT() with CASE"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
