How to get MTD sum and divide by individual group

Question

Post reply

How to get MTD sum and divide by individual group

agbondoc

Old Hand

Points: 366
More actions
January 24, 2012 at 5:30 am

#250186

Hi,
I want to express the following in a qry but I am having some trouble on how to write it, below is the issue...
Step 1. sum header3 and group by header1 and header3
Step 2. divide results from Step 1 to totals from different groups. This step would be the final results.
example...
Step 1
header1 header2 header3
group1 200
Step 2.
header1 header2 header3
group1 type-a .50
group1 type-b .25
group1 type-c .25
table 1.
header1 header2 header3
group1 type-a 100
group2 type-a 200
group3 type-a 300
group1 type-b 50
group1 type-c 50
group3 type-a 100
group3 type-d 50
Thanks
Art

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Can you please provide sample data and table DDL in the form of SQL statements. See this article for reference:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 2

CELKO (1/24/2012)
You then ask us to divide these into the foo_types, but we have no rules for that division. We have no idea how many foo_types and if they are required!
Do you program from specs like that that? How? Try again, the right way.

This is clearly a newbie to the forum by looking at their stats... No reason to be so rude.

Jared
CE - Microsoft

Paul White SSC Guru Points: 150467 More actions · Answer 3

Here's the sample code from your first post, together with a starting point for a solution:

Sample data:

DECLARE @Example TABLE

(

header1 char(6) NOT NULL,

header2 char(6) NOT NULL,

header3 integer NOT NULL

)

INSERT @Example

(header1, header2, header3)

VALUES

('group1', 'type-a', 100),

('group2', 'type-a', 200),

('group3', 'type-a', 300),

('group1', 'type-b', 50),

('group1', 'type-c', 50),

('group3', 'type-a', 100),

('group3', 'type-d', 50);

Solution:

SELECT

e.header1,

e.header2,

total = SUM(e.header3)

FROM @Example AS e

GROUP BY

e.header1,

e.header2;

Output:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

agbondoc Old Hand Points: 366 More actions · Answer 4

Thanks Jared.

You are correct that I am new to this forum and I will read (if time permits) the reference you sent and educate myselt on the "forum standards". I take on offense on Mr. Celko's post but I will say, however, that is a good way to alienate newbies like me to shy away from forums like this and search for other more "friendlier" blogs that encourages newbies or not to ask questions and not get "shot down".

Thanks.

Art

Jared SSC Guru Points: 61793 More actions · Answer 5

agbondoc (1/25/2012)
Thanks Jared.
You are correct that I am new to this forum and I will read (if time permits) the reference you sent and educate myselt on the "forum standards". I take on offense on Mr. Celko's post but I will say, however, that is a good way to alienate newbies like me to shy away from forums like this and search for other more "friendlier" blogs that encourages newbies or not to ask questions and not get "shot down".
Thanks.
Art

We've all had to be a Newbie at some point by definition 🙂 Stick around here, this is a great forum and you will get some of the best advice in the world. CELKO is a robot, I think... So just ignore it 😀

Was Paul able to answer your question in his post above?

Jared
CE - Microsoft

agbondoc Old Hand Points: 366 More actions · Answer 6

Thanks Jared,

Paul's post was very helpful. I can get to his results on my own w/out a problem - but my challenge is how do I then get the total for each group w/out header2 and divide that number to Paul's results. It almost seems like I should create a temp table with totals for the group and use that table to divide to Paul's results.

I can post later the tables or samples of what i am trying to accomplish.

Thanks

Art

agbondoc Old Hand Points: 366 More actions · Answer 7

Thanks Paul for the solution.

I will try and post samples later of what I am trying to accomplish - but you are right on the money with your solution, i just need one more piece to get it to work.

Thanks

Art

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

Another guess, just to pass the time:

SELECT

e.header1,

header2 = CASE WHEN GROUPING(e.header2) = 1 THEN 'Total' ELSE e.header2 END,

total = SUM(e.header3)

FROM @Example AS e

GROUP BY

GROUPING SETS

(e.header1, e.header2),

(e.header1);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi