May 4, 2005 at 6:42 am
Hi everybody
I'm new here and need some help.
This my table :
LA Operator Delivered Undelivered
Cellact Cellcom 24 8
Cellact Partner 14 2
Cellact Pelephone 23 6
Microsoft Cellcom 12 1
Microsoft Pelephone 13 2
And this is the result I'm looking for :
LA Operator Delivered Undelivered
Cellact Cellcom 12 4
Pelephone 23 6
Partner 14 2
Total 49 12
Microsoft Cellcom 12 1
Pelephone 13 2
Total 25 3
Total Cellcome 24 5
Pelephone 36 8
Partner 14 2
Total 74 15
The closest I got is by using the following query :
SELECT
CASE
WHEN (GROUPING(LA)=1) AND (GROUPING(Operator)=1) THEN ''
WHEN (GROUPING(LA)=1) AND (GROUPING(Operator)=0) THEN 'Total'
WHEN (GROUPING(LA)=0) AND (GROUPING(Operator)=1) THEN ''
ELSE LA
END AS 'Large Acount',
CASE
WHEN (GROUPING(LA)=0) AND (GROUPING(Operator)=1) THEN 'Total'
WHEN (GROUPING(LA)=1) AND (GROUPING(Operator)=1) THEN 'Total'
ELSE Operator
END AS 'Operator Name',
SUM(Delivered) AS 'Delivered', SUM(Undelivered) AS 'Undelivered'
FROM Table
GROUP BY LA, Operator
WITH CUBE
Which generated the following result :
LA Operator Delivered Undelivered
Cellact Cellcom 24 8
Cellact Partner 14 2
Cellact Pelephone 23 6
Total 61 16
Microsoft Cellcom 12 1
Microsoft Pelephone 13 2
Total 25 3
Total 86 19
Total Cellcom 36 9
Total Partner 14 2
Total Pelephone 36 8
Is there any way to get it exactly the way I want it to be ?
Thank you very much for your help
Sagi
May 6, 2005 at 8:07 am
Sorry to not be of much help but I did fool around with it a bit. I do know the "order by" statement alters the output immensely. I also played with a union statement for just appending the Grand Total to the end. Have you tried the Union Statement?
June 16, 2005 at 3:51 pm
Came across this. Maybe it would help. You can select the "Grouping" Value and also change your ordering of the output.
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 19, 2005 at 9:49 pm
I think this is what you are looking for.
HTH Mike
SELECT CASE WHEN (GROUPING(LA) =1) THEN 'TOTAL'
ELSE (LA)
END AS LA,
CASE WHEN (GROUPING(Operator)=1) THEN 'ALL'
ELSE(Operator)
END AS Operator,
SUM(Delivered) AS Delivered,
SUM(Undelivered) as Undelivered
FROM Test
GROUP BY La,Operator WITH CUBE
/*
LA Operator Delivered Undelivered
-------------------- ----------- ----------- -----------
Cellact Cellcom 24 8
Cellact Partner 14 2
Cellact Pelephone 23 6
Cellact ALL 61 16
Microsoft Cellcom 12 1
Microsoft Pelephone 13 2
Microsoft ALL 25 3
TOTAL ALL 86 19
TOTAL Cellcom 36 9
TOTAL Partner 14 2
TOTAL Pelephone 36 8
June 20, 2005 at 7:20 am
Have you tried using rollup rather than cube. Which returns:
LA Operator Delivered Undelivered
-------------------- ----------- ----------- -----------
Cellact Cellcom 24 8
Cellact Partner 14 2
Cellact Pelephone 23 6
Cellact Total 61 16
Microsoft Cellcom 12 1
Microsoft Pelephone 13 2
Microsoft Total 25 3
Total 86 19
If you need the exact formant you posted then I would suggest you do it client side
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply