SQL Help Required!!

Question

SQL Help Required!!

sarran.achari

SSCrazy

Points: 2226
More actions
January 1, 2009 at 3:33 am

#203399

Hi Gurus,
I have my data like seen below
Month Country Tran
Jul 2003 US 1271
Jun 2003 UK 1107
Aug 2003 FR 1096
Jul 2003 UK 1216
Jun 2003 US 1104
Aug 2003 UK 1094
Jul 2003 FR 1321
Jun 2003 FR 1210
Aug 2003 US 1109
I need a additional column that should give me percentage of contribution of country. I.e, For first record my column should contains
Tran / (All country tran for particular month).
How do i this?

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

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

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

Can you post a sample of what you expect your output to look like?

^{“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

sarran.achari SSCrazy Points: 2226 More actions · Answer 2

Am looking for output like

Month Country Tran Column1

Jul 2003 US 1271 0.33 (1271/sum(jul2003-US+jul2003-Uk+jul2003-FR)

Jun 2003 UK 1107 0.32 (1107/sum(jun2003-US+jun2003-Uk+jun2003-FR)

Aug 2003 FR 1096 0.33

Jul 2003 UK 1216 0.32

Jun 2003 US 1104 0.32

Aug 2003 UK 1094 0.33

Jul 2003 FR 1321 0.35

Jun 2003 FR 1210 0.35

Aug 2003 US 1109 0.34

My DB is SQL 2005

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

I reckon this should do it.

[font="Courier New"]DROP TABLE #Temp

CREATE TABLE #Temp ([Month] CHAR(8), Country CHAR(2), [Tran] INT)

INSERT INTO #Temp ([Month], Country, [Tran])

SELECT 'Jul 2003', 'US', 1271 UNION ALL

SELECT 'Jun 2003', 'UK', 1107 UNION ALL

SELECT 'Aug 2003', 'FR', 1096 UNION ALL

SELECT 'Jul 2003', 'UK', 1216 UNION ALL

SELECT 'Jun 2003', 'US', 1104 UNION ALL

SELECT 'Aug 2003', 'UK', 1094 UNION ALL

SELECT 'Jul 2003', 'FR', 1321 UNION ALL

SELECT 'Jun 2003', 'FR', 1210 UNION ALL

SELECT 'Aug 2003', 'US', 1109

SELECT a.*, d.TranMonthTotal, a.[Tran]/(d.TranMonthTotal*1.00) AS CountryContribution

FROM #Temp a

INNER JOIN (SELECT [Month], SUM([Tran]) AS TranMonthTotal

FROM #Temp

GROUP BY [Month]) d

ON d.[Month] = a.[Month]

[/font]

Check the link below, it will show you how to create sample data and table structures to help others answer your questions.

Cheers

ChrisM

^{“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

Dbs-887309 Old Hand Points: 342 More actions · Answer 4

The Best way to handle this is..

Instead of storing the value as column in table

Write a Function which will return the contribution percentage

example F_GET_COUNTRY_CONTRIBUTION ( country_name, Year)

By passing the country name and year you can get the value..

If you add more rows, everytime creating pivot is not practical

Venkat

sarran.achari SSCrazy Points: 2226 More actions · Answer 5

sarran.achari

SSCrazy

Points: 2226

January 1, 2009 at 5:14 am

#919935

Thanks for your help Gurus.

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

sarran.achari (1/1/2009)
Thanks for your help Gurus.

You're welcome. Is your problem solved?

^{“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

sarran.achari SSCrazy Points: 2226 More actions · Answer 7

Yes. Actually I wanted that additional column is SSRS.. but couldn't do it there.. so thot of doing it in the query itself

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 8

Venkat (1/1/2009)
The Best way to handle this is..
Instead of storing the value as column in table
Write a Function which will return the contribution percentage
example F_GET_COUNTRY_CONTRIBUTION ( country_name, Year)
By passing the country name and year you can get the value..
If you add more rows, everytime creating pivot is not practical
Venkat

I don't see where a pivot was asked for.

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