Hello experts,
I have to create a crosstab that would calculate the same field two ways.
This is what it has to look like:
Void Printed Cashed
count amount count amount count amount
1-May-09 4 $567.09 3 $543.98 5 $5,643.54
2-May-09 5 $342.06 4 $343.87
3-May-09 3 $543.97 4 $435.76
4-May-09 5 $34.99 1 $342.96
The trick is, I have to use the same columns to calculate the count and the amount. Is there a way to do it?
Any help greatly appreciated
LP
Based on personal preference: try a union:
SELECT DATE,TYPE,'COUNT' as AGGR_type,COUNT(<<SOME_OBJECT>>) AS MEASURE
WHERE.......
GROUP BY DATE,TYPE
UNION
SELECT DATE,TYPE,'AMOUNT' as AGGR_type,SUM(<<SOME_OBJECT>>) AS MEASURE
WHERE.......
GROUP BY DATE,TYPE
Okay, the example is drawn-up in SQL
The essence is that the union returns both the count and the sum on a given object.
The fixed column AGGR_type is then used in the crosstab as a dimension nested below the type dimension ('Void','Printed','Cashed')