Cannot perform an aggregate function ERROR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Cannot perform an aggregate function ERROR

Author

Topic

k9-961
Starting Member

6 Posts

Posted - 2011-09-27 : 16:56:58

Error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

trying to mimic MS Access expression from a query converted to SQL Server using SSMA.

Access Query Expression:
OBS %: Round(IIf(Sum(IIf([VisitType]="IP" And [Flag]<>"S",1,0))>0,Sum(IIf([VisitType]="OBS",1,0))/(Sum(IIf([VisitType]="IP" And [Flag]<>"S",1,0))+Sum(IIf([VisitType]="OBS",1,0))),0),3)

Need to perform a percent column calculation (divide one case value by the other case value) in a SQL View, but can't get the correct values.

Calc_Field = Sum((
(CASE
WHEN
[VisitType] = 'OBS' AND
[Flag] <> 'S' THEN 1
ELSE 0
END)) /
Sum((
CASE
WHEN
[VisitType] = 'IP' AND
[Flag] <> 'S' THEN 1
ELSE 0
END)))

In MS Access the IIF function was creating the percentages using:

Day%: Round(IIf(Sum(IIf([VisitType]="IP" And [Flag]<>"S",1,0))>0,Sum(IIf([VisitType]="IP" And [Flag]<>"S"))/Sum(IIf([VisitType]="IP" And [Flag]<>"S",1,0)),0),3)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 17:38:39

Does this work?


select Calc_Field = 
       Sum(CASE 
              WHEN [VisitType] = 'OBS' AND [Flag] <> 'S' THEN 1
              ELSE 0
              END)
       /
       nullif(sum(CASE
              WHEN [VisitType] = 'IP' AND [Flag] <> 'S' THEN 1.0
              ELSE 0.0
              END),0)

Be One with the Optimizer
TG

k9-961
Starting Member

6 Posts

Posted - 2011-09-28 : 10:10:13

That helped! (see values below) But how do I format the values to only show up as 23%, 04%, & 49% for the values below that are now showing?
Calc Field
23.38129496402877697841727
3.70989304812834224598930
48.74699622382423618262959

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 10:34:57

It is always best to do the formatting in whatever application you use to present this data.

One question though - wouldn't this (3.709) be %370.9 ?
ie: select 5/10.0 = 0.500000 = %50

To answer your question about formatting in sql (bad idea - do it in front end) you can use CONVERT. Maybe this:


declare @v numeric(38,9)
set @v = 0.38129496402877697841727
select '%' + convert(varchar(12), convert(numeric(38,2), (@v*100)))

OUTPUT:
%38.13

Be One with the Optimizer
TG

k9-961
Starting Member

6 Posts

Posted - 2011-09-29 : 09:54:33

I get good results with most queries, but on some I am getting the ERROR "Divide by zero error encountered." How can I change tyhe CASE statement to look for this? This is what I have currently:

CAST(Round(SUM(CASE
WHEN [VisitType] = 'OBS' THEN 1
ELSE 0
END) / Nullif (SUM(CASE
WHEN ( [VisitType] = 'IP'
AND [Flag] <> 'S' )
OR ( [VisitType] =
'OBS' )
THEN
1.0
ELSE 0.0
END), 4), 4) AS FLOAT) AS [Calc Field %]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-29 : 10:37:17

Looks like you changed the NULLIF (<exp>, 0) to (<exp>, 4). The method I used to avoid the devide by zero error was to make the denominator NULL rather than 0.

Be One with the Optimizer
TG

k9-961
Starting Member

6 Posts

Posted - 2011-09-29 : 11:19:43

Oh yeah... Nice catch. What if I need the NULL values to be zeros?

Thank you for all your help!!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-29 : 11:43:10

You can use ISNULL(<expression>, 0) to change a NULL to 0.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources