November 5, 2017 at 8:53 am
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
November 5, 2017 at 8:55 am
the output from the above query is attached for your information
November 5, 2017 at 12:39 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 6, 2017 at 8:40 am
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
November 6, 2017 at 3:50 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply