| 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 |
|
|
|
|
Cannot perform an aggregate function ERROR