cancel
Showing results for 
Search instead for 
Did you mean: 

cube clause

1,224

Hi,

I use the cube clause as the documentation show, for exemple with this query :

SELECT QUARTER( OrderDate ) AS Quarter,
   YEAR( OrderDate ) AS Year,
   COUNT( * ) AS Orders,
   GROUPING( Quarter ) AS GQ,
   GROUPING( Year ) AS GY 
FROM SalesOrders GROUP BY CUBE ( Year, Quarter )
ORDER BY Year, Quarter;

The result is almost perfect for me. If I have no orders in a quarter or in a year, I have no row for it. In these case I would have a row with the value of order at 0. Is it possible to do that ?

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

If you provide a table or table expression covering all combinations of your cube dimensions, OUTER JOIN it with the base table as the inner table, and finally COUNT against a column in the inner table (which would be treated as NULL for non-matches), this should provide you the desired result.

HTH
Volker

Evolution of statement:

with teQ ("Quarter") as (select distinct quarter (OrderDate) from SalesOrders),
     teY ("Year") as (select distinct year (OrderDate) from SalesOrders)
select teQ."Quarter", teY."Year"
from teQ cross join teY
order by teY."Year", teQ."Quarter";

Adding OUTER JOIN

with teQ ("Quarter") as (select distinct quarter (OrderDate) from SalesOrders),
     teY ("Year") as (select distinct year (OrderDate) from SalesOrders)
select teQ."Quarter", teY."Year", count (so.OrderDate)
from (teQ cross join teY) left outer join SalesOrders so on quarter (so.OrderDate) = teQ."Quarter" and year (so.OrderDate) = teY."Year"
group by cube (teQ."Quarter", teY."Year")
order by teY."Year", teQ."Quarter";

Adding GROUPING expressions

with teQ ("Quarter") as (select distinct quarter (OrderDate) from SalesOrders),
     teY ("Year") as (select distinct year (OrderDate) from SalesOrders)
select teQ."Quarter", teY."Year", count (so.OrderDate), grouping (teQ."Quarter") as GQ, grouping (teY."Year") as GY
from (teQ cross join teY) left outer join SalesOrders so on quarter (so.OrderDate) = teQ."Quarter" and year (so.OrderDate) = teY."Year"
group by cube (teQ."Quarter", teY."Year")
order by teY."Year", teQ."Quarter";

I'd expect this to become a performance nightmare for non-trivial data sets; I'd expect this to be more robust

with baseResult ("Quarter", "Year", "SubTotal")
  as (select quarter (OrderDate) as "Quarter", year ("OrderDate") as "Year", count (OrderDate) as Subtotal
        from SalesOrders group by "Quarter", "Year"),
     teQ ("Quarter") as (select distinct "Quarter" from baseResult),
     teY ("Year") as (select distinct "Year" from baseResult)
select teQ."Quarter", teY."Year", sum (isnull (baseResult.SubTotal, 0)) as Orders, grouping (teQ."Quarter") as GQ, grouping (teY."Year") as GY
from (teQ cross join teY) left outer join baseResult on baseResult."Quarter" = teQ."Quarter" and baseResult."Year" = teY."Year"
group by cube (teQ."Quarter", teY."Year")
order by teY."Year", teQ."Quarter";

If this doesn't perform good enough, materializing the table expressions into local temp tables should help.

VolkerBarth
Contributor

Nice approach!

So, to answer the underlying question:

A GROUP BY can only group existing rows from the underlying result set. If a row is not part of the underlying result set, it cannot be added via GROUP BY clauses, it must be added to the underlying result set itself - like Volker has shown above.

0 Kudos

Thanks for your responses I will try this :).