total of sum
-
"Unordered"... have you an example of when ROLLUP produces incorrectly ordered results?
Also, what do you mean by it not taking us "very far forward"? unless I'm misunderstanding the requirements given by the OP, it does the same as the SUM(SUM(Field)) he was looking for.Sorry... Never mind the stuff in the strikeout above... I missed your original "Grand total" in the previous example when I was looking at the posts.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/18/2011)
"Unordered"... have you an example of when ROLLUP produces incorrectly ordered results?I don't need one: presentation order is never guaranteed unless there is an outer-scope ORDER BY clause. By the way (and I know you'll like this) did you know the WITH ROLLUP syntax is deprecated? The new (ISO-compliant) form is:
SELECT
e.id,
SUM(e.amount) AS id_sum,
SUM(SUM(e.amount)) OVER () AS total,
SUM(e.amount) / SUM(SUM(e.amount)) OVER () AS proportion
FROM @Example AS e
GROUP BY
ROLLUP (e.id)
See http://msdn.microsoft.com/en-us/library/ms177673.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
You can use rollup like this,
SELECT
CASE
WHEN GROUPING(id) = 1 THEN 'Total'
ELSE CONVERT(VARCHAR(12), id)
END AS id,
SUM(amount) AS Sales
FROM @Example
GROUP BY ROLLUP(Id)
-
hi,
You can check the below example, it might help you in framing your query.
Create Table test1
( c1 int ,
C2 int)
insert into test1 values (1,45)
insert into test1 values (1,76)
insert into test1 values (1,98)
insert into test1 values (2,130)
insert into test1 values (2,156)
insert into test1 values (2,112)
insert into test1 values (3,23)
insert into test1 values (3,150)
select c1,sum(C2) from
test1
group by c1 with rollup
The ourput will be like this,
c1summ
1219
2398
3173
NULL790
The row with column C1 gives the sum of all...
thanks
sarat 🙂
Curious about SQL -
SQL Kiwi (9/18/2011)
Jeff Moden (9/18/2011)
"Unordered"... have you an example of when ROLLUP produces incorrectly ordered results?I don't need one: presentation order is never guaranteed unless there is an outer-scope ORDER BY clause. By the way (and I know you'll like this) did you know the WITH ROLLUP syntax is deprecated? The new (ISO-compliant) form is:
SELECT
e.id,
SUM(e.amount) AS id_sum,
SUM(SUM(e.amount)) OVER () AS total,
SUM(e.amount) / SUM(SUM(e.amount)) OVER () AS proportion
FROM @Example AS e
GROUP BY
ROLLUP (e.id)
Understood on the deprecation and thanks for the reminder.
Although I'd normally agree about the "only ORDER BY can guarantee the order of the output) thing, I disagree about the "ORDER BY" in this case. Rollup and Cube are designed to return the aggregates and related subtotals as "GROUPING SETS" in a certain order according to the right to left order of the GROUP BY column order (in the case of the deprecated form) or the right to left GROUPING column order (in the case of the "new" compliant method). So I'll have to agree to disagree with you on this one especially since, to the best of my knowledge, no one has been able to produce a scenario where a properly formed "grouping" query has created an other than correct sort order. If someone can actually cause and demonstrate a properly formed "grouping" to produce an out of order result set, then I'll carry the same banner as you on the subject.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/19/2011)
Although I'd normally agree about the "only ORDER BY can guarantee the order of the output) thing, I disagree about the "ORDER BY" in this case. Rollup and Cube are designed to return the aggregates and related subtotals as "GROUPING SETS" in a certain order according to the right to left order of the GROUP BY column order (in the case of the deprecated form) or the right to left GROUPING column order (in the case of the "new" compliant method).Do you have a reference for this 'certain order'?
So I'll have to agree to disagree with you on this one especially since, to the best of my knowledge, no one has been able to produce a scenario where a properly formed "grouping" query has created an other than correct sort order. If someone can actually cause and demonstrate a properly formed "grouping" to produce an out of order result set, then I'll carry the same banner as you on the subject.
Try this:
SELECT
e.id,
SUM(e.amount) AS id_sum,
SUM(SUM(e.amount)) OVER () AS total,
SUM(e.amount) / SUM(SUM(e.amount)) OVER () AS proportion,
rn = ROW_NUMBER() OVER (ORDER BY MIN(amount))
FROM @Example AS e
GROUP BY ROLLUP (e.id)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
my question is after i do proportion .again i need to do sum of all proportion and i need to display in row
how to do that
-
daveriya (9/19/2011)
my question is after i do proportion .again i need to do sum of all proportion and i need to display in row how to do thatThis is one way:
SELECT
id =
CASE
WHEN GROUPING(e.id) = 1 THEN 'Total'
ELSE CONVERT(VARCHAR(12), e.id)
END,
id_sum = SUM(e.amount),
proportion = SUM(e.amount) /
SUM(CASE WHEN GROUPING(id) = 0 THEN SUM(e.amount) END) OVER ()
FROM @Example AS e
GROUP BY
ROLLUP (e.id)
ORDER BY
GROUPING(id),
e.id
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
nobody understand my prob here,i past my query also, the thing is in my query i need to calculate WAL,
that is only value i need to display ,i cant use case statement also.
-
daveriya (9/19/2011)
nobody understand my prob here,i past my query also, the thing is in my query i need to calculate WAL, that is only value i need to display ,i cant use case statement also.The only reason no-one understands your problem is that you haven't communicated it well - for whatever reason. You have to remember that none of us can see your database - so we have to guess at what you mean. There are two main ways forward here:
(1) Write a simple script that gives us some data to work on and show the exact output you require; or
(2) Find someone locally that can sit down with you and help.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
daveriya (9/19/2011)
nobody understand my prob here,i past my query also, the thing is in my query i need to calculate WAL, that is only value i need to display ,i cant use case statement also.
The only reason no-one understands your problem is that you haven't communicated it well - for whatever reason. You have to remember that none of us can see your database - so we have to guess at what you mean. There are two main ways forward here:
(1) Write a simple script that gives us some data to work on and show the exact output you require; or
(2) Find someone locally that can sit down with you and help.
Paul White
Yes Paul, I do agree with you.
And I really liked your approaches to help this guy.
Regards,
Sudhir
-
SQL Kiwi (9/19/2011)
Do you have a reference for this 'certain order'?I do. As a matter of fact, it's in the link you posted.
Try this:
SELECTe.id,
SUM(e.amount) AS id_sum,
SUM(SUM(e.amount)) OVER () AS total,
SUM(e.amount) / SUM(SUM(e.amount)) OVER () AS proportion,
rn = ROW_NUMBER() OVER (ORDER BY MIN(amount))
FROM @Example AS e
GROUP BY ROLLUP (e.id)
BWAA-HAAA!!! I certainly agree that you can use an ORDER BY to disrupt the order. Ok... I give. 🙂 In the light of what people could do in the SELECT, I agree that an "external" ORDER BY is necessary.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/19/2011)
I do. As a matter of fact, it's in the link you posted.The only bit I could find was this:
Columns are rolled up from right to left. The column order affects the output groupings of ROLLUP and can affect the number of rows in the result set.
It doesn't say anything about output ordering, just logical processing. The same sort of guidance is absent from CUBE of course. Anyway, I don't think it's worth pursuing in detail, so let's leave it there.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
That's part of it but I agree... it's doesn't explicity state a given order so we'll leave it where it lays.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SQL Kiwi (9/19/2011)
This is one way:SELECT
id =
CASE
WHEN GROUPING(e.id) = 1 THEN 'Total'
ELSE CONVERT(VARCHAR(12), e.id)
END,
id_sum = SUM(e.amount),
proportion = SUM(e.amount) /
SUM(CASE WHEN GROUPING(id) = 0 THEN SUM(e.amount) END) OVER ()
FROM @Example AS e
GROUP BY
ROLLUP (e.id)
ORDER BY
GROUPING(id),
e.id
That's such a sexy little code, Paul. :w00t:
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply