Report to include two count fields!

[sammybee77]

Hi,

I'm writing a report in SQL, which I would like to do a count of a field where another field = condition a and then count again off the same table where same field = condition b. The table I am reporting off is outer joined to my main table. Any ideas?

 

[Scunningham99]

why not use a lexical param on a condition, or use another query, so your report has the same query asain.

and in your layout edditor use a program unit to display true based on condition.

 

[ddt]

you can easily do this with a decode inside a sum function
sum(decode(fielda, 'value1', 1,0)) as COUNT_OF_A
says everytime fielda = 'value1' add 1 to the sum otherwise add a 0 and name this sum COUNT_OF_A.
your other count is
sum(decode(fielda, 'value2', 1, 0)) as COUNT_OF_2