crosstab calculations

[Little Princess]

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

[blom0344]

Based on personal preference: try a union:

Code Select Expand


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')