|
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 |
|
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 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 '19, 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 '19, 06:50)
Volker Barth
Thanks for your responses I will try this :).
(28 Aug '19, 08:25)
Ben8sens
|
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 0Please show us exactly what you want to see... thanks.
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 :
(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 10The doc has been corrected. Thanks for noting this.
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 :)