June 14, 2005 at 2:47 am
Could Someone explain plz, how does the fillowing script work?
i dont quite understand the syntax eg
the case statemenent
the having grouping (store_state)=0
order by ... grouping (store_name)
although i can see the results (foodmart) i cant quite realize the script. some help plz.
here is the script
SELECT CASE GROUPING(store_name)
WHEN 1 then '[State total]'
ELSE store_name
End as store,
store_state,
sum(unit_sales)
FROM store s
JOIN sales_fact_1997 sl on s.store_id = sl.store_id
GROUP BY store_name, store_state
WITH CUBE
HAVING GROUPING(store_state) = 0
ORDER BY store_state, GROUPING(store_name)
June 14, 2005 at 3:27 am
1.
Grouping is an aggregate function that causes an additional column to be output with a value of 1
when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.
example
royalty total advance grp
--------- --------------------- ---
NULL NULL 0
10 57000.0000 0
12 2275.0000 0
14 4000.0000 0
16 7000.0000 0
24 25125.0000 0
NULL 95400.0000 1
The result set shows two null values under royalty.
The first NULL represents the group of null values from
this column in the table. The second NULL is in the
summary row added by the ROLLUP operation.
The summary row shows the total advance amounts for
all royalty groups and is indicated by 1 in the grp column.
2.
having grouping (store_state)=0 i.e it should not take sum ot total of all store_state.
from above example it will not display last row.
3. order by is usual.
hth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply