Correct sql for summing a group of records and giving proportion of each value across group

Question

Post reply

Correct sql for summing a group of records and giving proportion of each value across group

JimReeves71

Default port

Points: 1410

November 5, 2017 at 8:53 am

#326146

I have a set of records which is grouped by Regions . ie the following

Africa
Western Europe
North America
Middle East

Now within each of the regions listed above , there are a list of countries with values
Ie Western Europe has the following

France

Netherlands

Denmark

Italy

Sweden

Rest of Western Europe

United Kingdom

Now i want to be able to within each region, sum a set of values for each region

to get the following :

combo	Region	Country	totalValue	GroupTotal	Market Share
4	Western Europe	France	2471	149827	1.65%
4	Western Europe	Netherlands	64352	149827	42.95%
4	Western Europe	Denmark	2065	149827	1.38%
4	Western Europe	Italy	141	149827	0.09%
4	Western Europe	Sweden	67601	149827	45.12%
4	Western Europe	Rest of Western Europe	1858	149827	1.24%
4	Western Europe	United Kingdom	11339	149827	7.57%
			149827	149827

So the group total for Western Europe is 149827 and the market share is totalvalue/Group total
However im getting the following

combo	totalValue	GroupTotal	marketshare
4	2471	2471	100
4	64352	64352	100
4	2065	2065	100
4	141	141	100
4	67601	67601	100
4	1858	1858	100
4	11339	11339	100

This is the sql im using to try and extract this
How can i correctly partion the records?

SELECT

'4' AS Combo

,Dataset

,DatasetID

,Region

,SUM(Value) AS totalValue

,SUM(SUM(Value)) OVER (PARTITION BY b.country,b.month, Services, b.metric) AS GroupTotal

,ROUND(SUM(Value) / (SUM(SUM(Value)) OVER (PARTITION BY b.region,b.country,dataset, datasetid,b.country,b.month, Services, b.metric)) * 100, 2) AS marketshare

,b.Country

,Services

,b.Metric

,[Metric Type]

,Unit

,b.[Month]

,MAX(Publisheddate) Publisheddate

,MAX(Scenario) Scenario

,MAX(Forecastversion) Forecastversion

,MAX(Exchangeratedate) Exchangeratedate

,'Computed' Version

,CASE

WHEN DP.NUMBER_OF_SOURCES > 1 THEN --If sources at data points are more than one, the turn source to 'Market Intelligence'

@VarSourceMoreThanOne

ELSE

B.Source

END AS SOURCE

FROM

[dbo].[Fact_Forecaster_Yearly] b

INNER JOIN

#T_Sources_Data_Points DP ON B.Month = DP.MONTH

AND

b.Metric = dp.Metric

WHERE [Metric Type] IN ('Cumulative', 'In Period Total')

AND DataSourceid < 3

GROUP BY Dataset

,DatasetID

,b.Metric

,[Metric Type]

,region

,b.Country

,services

,Unit

,b.month

,DP.NUMBER_OF_SOURCES

,b.Source

Viewing 5 posts - 1 through 4 (of 4 total)

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

JimReeves71 Default port Points: 1410 More actions · Answer 1

the output from the above query is attached for your information

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

With >500 points, you're clearly not a newcomer here and should know by now that you need to provide sample DDL + INSERT statements for test data + desired results, if you are hoping to get a working solution.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 3

It's because you're using the wrong PARTITION BY clause. It's not clear what the correct one is, but you should NOT include Country in the partition. Also, one of your PARTITION BY clauses includes Country twice. Adding it a second time won't change the partition.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

chris.asaipillai-624309 - Sunday, November 5, 2017 8:53 AM

This posting is a bad combination of rudeness and ignorance. Rows are nothing like records. Then you completely ignore the basic netiquette of all SQL forms the last 35+ years, and fail to post DDL.

>> I have a set of records which is grouped by Regions . ie the following

CREATE TABLE Regions
(region_name VARCHAR(20) NOT NULL PRIMARY KEY,
country_name VARCHAR(20) NOT NULL);

Actually this is pretty bad because there is an ISO standard three letter country abbreviation that should of been used instead of a name. But let's go ahead and leave it.

>> Now within each of the regions listed above , there are a list of countries with values <<

Valid specifications would tell us if a country can belong to more than one region. For example, is Turkey in Europe or in Asia? Or both?

>> Now I want to be able to within each region, sum a set of values for each region

to get the following : <<

The picture you posted instead of the DDL required by basic netiquette tells us nothing. We have no idea what were summing or doing math on!

>> So the group total for Western Europe is 149827 and the market share is totalvalue/Group total <<

Total of what?? Where's that value in the tables, you never posted! You seem to expect us to read your mind. If you're working at a company for you have to program specs like this, it is time to debate your resume, tell your boss that he's an idiot, and find another job.

Please post DDL and follow ANSI/ISO standards when asking for help.