sum of columns based on new group

Question

Post reply

sum of columns based on new group

Viewing 6 posts - 16 through 20 (of 20 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

dwain.c (12/23/2013)

Brilliant Jeff, simply brilliant!

Tops my offering by a mile:

;WITH Subproducts AS (



	SELECT PrimaryID = LEFT([Product Id],CHARINDEX('.',[Product Id],0)-1), *

	FROM #Product_Table

	WHERE IsPrimary <> 1)

SELECT

	PrimaryID,

	Subproduct = '',

	quantity = CAST(EXP(SUM(LOG(quantity))) AS INT)

FROM Subproducts

GROUP BY PrimaryID

UNION ALL

SELECT

	PrimaryID,

	MAX([Product Id]),

	0

FROM Subproducts

GROUP BY PrimaryID

ORDER BY PrimaryID, Subproduct

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains two types of data too, and I'd guess it only looks that way in an attempt to model the source data...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

ChrisM@Work (12/23/2013)

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains two types of data too, and I'd guess it only looks that way in an attempt to model the source data...

Good point but no. The problem is that the table likely does exist in that form and, despite our concerns and as incorrect as it may be, the OP likely didn't make up the requirement. It was probably given to him and he needed our help. I just hope he goes back to whom ever the original designer was and the people giving him the requirement for this type of output and says "Here's what the people over at SQLServerCentral had to say about your design/requirements. You might want to fix them".

--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

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

dwain.c (12/23/2013)

Brilliant Jeff, simply brilliant!

Thanks for the feedback, Dwain but it's not that. It was late and I had to do it with just dip on the table because I was too tired to figure out how to do a self join. 😀

--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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jeff Moden (12/23/2013)

ChrisM@Work (12/23/2013)

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains two types of data too, and I'd guess it only looks that way in an attempt to model the source data...

Good point but no. The problem is that the table likely does exist in that form and, despite our concerns and as incorrect as it may be, the OP likely didn't make up the requirement. It was probably given to him and he needed our help...

It's all too easy to stomp on the OP when they post up what is clearly a poor storage choice - without establishing beforehand if they had anything to do with it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

ChrisM@Work (12/23/2013)

Jeff Moden (12/23/2013)

ChrisM@Work (12/23/2013)

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains two types of data too, and I'd guess it only looks that way in an attempt to model the source data...

Good point but no. The problem is that the table likely does exist in that form and, despite our concerns and as incorrect as it may be, the OP likely didn't make up the requirement. It was probably given to him and he needed our help...

It's all too easy to stomp on the OP when they post up what is clearly a poor storage choice - without establishing beforehand if they had anything to do with it.

Yeah... One person in particular was rather famous for that. I'm tickled that he doesn't appear to haunt these hallowed hallways anymore.

--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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Jeff Moden (12/23/2013)

ChrisM@Work (12/23/2013)

Jeff Moden (12/23/2013)

ChrisM@Work (12/23/2013)

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains two types of data too, and I'd guess it only looks that way in an attempt to model the source data...

Good point but no. The problem is that the table likely does exist in that form and, despite our concerns and as incorrect as it may be, the OP likely didn't make up the requirement. It was probably given to him and he needed our help...

It's all too easy to stomp on the OP when they post up what is clearly a poor storage choice - without establishing beforehand if they had anything to do with it.

Yeah... One person in particular was rather famous for that. I'm tickled that he doesn't appear to haunt these hallowed hallways anymore.

Heehee yes I know who you mean. Shame because he writes great articles. I remember Steve once saying that he was a really nice bloke to spend time with. His posts were strange for two reasons: the venom of the attacks, and the arcane nature of the detail. Folks post questions here very broadly speaking for two reasons too - they really want to learn, or they really don't - and that kind of attack offends both!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden