Sum statement subquery error within X-tab report 1

[GaryYay]

I have included the following lines in a Cross Tab report, which basically returns number of sales which pass quality assurance first time, and those which take more than one attempt (i.e. have to be requalified).

case when q.finishcode = 'sale' and count(q.i3_rowid) = 1 then 1 else 0 end as firsttimepasses,

case when q.finishcode = 'sale' and count(q.i3_rowid) > 1 then 1 else 0 end as requalifiedsales,

Each time a sale is QA'd, it writes a new history line to a QA table - either QA Fail, Sale or Permanent Fail.

This report returns several other pieces of information in summary form. Ideally what I want from these lines is to get a sum of the counts of first time passes and requalified sales - however when I try the following

sum(case when q.finishcode = 'sale' and count(q.i3_rowid) = 1 then 1 else 0 end) as firsttimepasses,

sum(case when q.finishcode = 'sale' and count(q.i3_rowid) > 1 then 1 else 0 end) as requalifiedsales,

I get the following:

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

Is there a way around this? Apologies for sounding thick.

Cheers

Gary

 

[gmmastros]

Make your original query be a subquery, and then do the sum in the outside query.

[tt][blue]
Select Sum(FirstTimePasses) As CountOfFirstTimePasses,
Sum(RequalifiedSales) As CountOfRequalifiedSales
From (
Select case when q.finishcode = 'sale' and count(q.i3_rowid) = 1
then 1
else 0
end as firsttimepasses,
case when q.finishcode = 'sale' and count(q.i3_rowid) > 1
then 1
else 0
end as requalifiedsales
From Table
Etc...
) As AliasName
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tazuk]

George
Old post I know but you've just saved my bacon, and my weekend.

TazUk

Programmer An organism that turns coffee into software.
Unknown Author