cube clause

[former_SQLA_member1694891]

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

[Breck_Carter]

The last row shown in the example in V17 the docs is wrong.

The actual V17 demo database result set is this:

    Quarter   Year      Orders          GQ          GY 
----------- ------ ----------- ----------- ----------- 
     (NULL) (NULL)         648           1           1 
          1 (NULL)         226           0           1 
          2 (NULL)         196           0           1 
          3 (NULL)         101           0           1 
          4 (NULL)         125           0           1 
     (NULL)   2000         380           1           0 
          1   2000          87           0           0 
          2   2000          77           0           0 
          3   2000          91           0           0 
          4   2000         125           0           0 
     (NULL)   2001         268           1           0 
          1   2001         139           0           0 
          2   2001         119           0           0 
          3   2001          10           0           0 

Please show us exactly what you want to see... thanks.

[former_SQLA_member1694891]

I want to see something like that in case I don't sold anything in the second quarter of 2000 or in the year 2001 :

Quarter   Year      Orders

---

 (NULL) (NULL)         648
      1 (NULL)         226
      2 (NULL)         196
      3 (NULL)         101
      4 (NULL)         125
 (NULL)   2000         303
      1   2000          87
      2   2000           0
      3   2000          91
      4   2000         132
 (NULL)   2001           0
      1   2001           0
      2   2001           0
      3   2001           0
      4   2001           0
 (NULL)   2002         268
      1   2002         139
      2   2002         119
      3   2002          10

[jack_schueler]

The doc has been corrected. Thanks for noting this.

[Breck_Carter]

Cool! ...thanks

Just so you know, the portal is my go-to source for up-to-date V17 facts. I still use HTML Help for stuff that hasn't changed in forever, but not DCX, not at all, not no more 🙂

[former_SQLA_member1694885]

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

[former_SQLA_member1694885]

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]

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.

[former_SQLA_member1694891]

Thanks for your responses I will try this :).