Sum statement subquery error within X-tab report

[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

 

[Catadmin]

The only way around it (that I know of) is to go into the Report Code and set up variables there. Some people say it doesn't work for them, though.

I take it you have SRS 2000?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[GaryYay]

Whoops, I think I posted this in the wrong place! Cheers anyway