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

asked 13 Aug, 11:06

Ben8sens's gravatar image

Ben8sens
864713
accept rate: 20%

1

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.

(13 Aug, 14:28) Breck Carter
Replies hidden

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

(14 Aug, 03:57) Ben8sens

The doc has been corrected. Thanks for noting this.

(14 Aug, 10:24) JBSchueler

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

(14 Aug, 11:12) Breck Carter

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

permanent link

answered 15 Aug, 04:36

Volker%20DB-TecKy's gravatar image

Volker DB-TecKy
5303615
accept rate: 26%

Comment Text Removed
1

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.

(15 Aug, 05:33) Volker DB-TecKy
1

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.

(15 Aug, 06:50) Volker Barth

Thanks for your responses I will try this :).

(28 Aug, 08:25) Ben8sens
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×56

question asked: 13 Aug, 11:06

question was seen: 166 times

last updated: 28 Aug, 08:25