total of sum

Question

Post reply

total of sum

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

"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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 2

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

hardikssit1 Say Hey Kid Points: 669 More actions · Answer 3

hardikssit1

Say Hey Kid

Points: 669

September 19, 2011 at 1:13 am

#1383168

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)

hunt Right there with Babe Points: 780 More actions · Answer 4

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,

c1 summ

1 219

2 398

3 173

NULL 790

The row with column C1 gives the sum of all...

thanks
sarat 🙂
Curious about SQL

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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)

See http://msdn.microsoft.com/en-us/library/ms177673.aspx

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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 6

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

daveriya SSCrazy Points: 2352 More actions · Answer 7

daveriya

SSCrazy

Points: 2352

September 19, 2011 at 9:09 am

#1383350

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

Paul White SSC Guru Points: 150457 More actions · Answer 8

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 that

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

daveriya SSCrazy Points: 2352 More actions · Answer 9

daveriya

SSCrazy

Points: 2352

September 19, 2011 at 11:49 am

#1383421

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.

Paul White SSC Guru Points: 150457 More actions · Answer 10

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

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

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:

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)

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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

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)
Intro to Tally Tables and Functions

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 15

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA