Tricky Calculation Part 2

Question

Post reply

Tricky Calculation Part 2

Viewing 6 posts - 1 through 7 (of 7 total)

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

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

Put another SELECT around it Dave, like this:

SELECT

CONTRY_NAME_LC,

Total,

Coordinate,

Street,

Hi_Res,

Street_Name,

Postcode,

District,

City,

County,

Region,

[State],

CRESTA,

Country,

[None]

FROM (

SELECT

CONTRY_NAME_LC,

COUNT(GeoResolutionCode) as Total,

SUM(case when GeoResolutionCode = 1 then 1 else 0 end) Coordinate,

SUM(case when GeoResolutionCode = 2 then 1 else 0 end) Street,

SUM(case when GeoResolutionCode = 3 then 1 else 0 end) Hi_Res,

SUM(case when GeoResolutionCode = 4 then 1 else 0 end) Street_Name,

SUM(case when GeoResolutionCode = 5 then 1 else 0 end) Postcode,

SUM(case when GeoResolutionCode = 6 then 1 else 0 end) District,

SUM(case when GeoResolutionCode = 7 then 1 else 0 end) City,

SUM(case when GeoResolutionCode = 8 then 1 else 0 end) County,

SUM(case when GeoResolutionCode = 9 then 1 else 0 end) Region,

SUM(case when GeoResolutionCode = 10 then 1 else 0 end) [State],

SUM(case when GeoResolutionCode = 11 then 1 else 0 end) CRESTA,

SUM(case when GeoResolutionCode = 14 then 1 else 0 end) Country,

SUM(case when GeoResolutionCode = 0 then 1 else 0 end) [None]

FROM EM_REFDATA.dbo.Cargo_Mapping

GROUP BY CONTRY_NAME_LC

) d

ORDER BY CONTRY_NAME_LC

The inner SELECT, your original query, is called a "derived table". The cost of doing this (as shown, with no aggregates) is very small. Now you have the values you need to work with in the same layer. Write the expressions in the output list of the outer SELECT.

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

methexis SSC Eights! Points: 815 More actions · Answer 2

So,

the first part of the query would look something like this?

SELECT

CONTRY_NAME_LC,

--Total,

SUM(Coordinate/Total*100)Coordinate,

SUM(Street/Total*100)Street,

SUM(Hi_Res/Total*100)Hi_Res,

SUM(Street_Name/Total*100)Street_Name,

SUM(Postcode/Total*100)Postcode,

SUM(District/Total*100)District,

SUM(City/Total*100)City,

SUM(County/Total*100)County,

SUM(Region/Total*100)Region,

SUM([State]/Total*100)State,

SUM(CRESTA/Total*100)CRESTA,

SUM(Country/Total*100)Country,

SUM([None]/Total*100)None

FROM (

SELECT

I had to take out the 'Total' as it did not want to work with that included in the select, makes sense...

This gives me some odd results either 0 or 100 which I am guessing could be an issue with data types?!

Thanks again!

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

Looks about right.

Try

SUM(Coordinate/Total*100.00) Coordinate

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

methexis SSC Eights! Points: 815 More actions · Answer 4

I placed this around the functions in the derived table

CAST (SUM(case when GeoResolutionCode = 1 then 1 else 0 end)AS FLOAT) Coordinate,

and then rounded them on the outer selection

ROUND(SUM(Coordinate/Total*100.00),2)Coordinate,

Seems to have done the trick!

Thanks so much for all your help, cant believe how much I am learning just asking a few questions on this forum!

:w00t:

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

dave_vicary (11/8/2011)
I placed this around the functions in the derived table
CAST (SUM(case when GeoResolutionCode = 1 then 1 else 0 end)AS FLOAT) Coordinate,
and then rounded them on the outer selection
ROUND(SUM(Coordinate/Total*100.00),2)Coordinate,
Seems to have done the trick!
Thanks so much for all your help, cant believe how much I am learning just asking a few questions on this forum!
:w00t:

FLOAT's a bit expensive for this - and it's an INT of one flavour or another. If I were you, I'd CAST the constant 100 to a suitable datatype, probably a DECIMAL(6,3) OR SIMILAR.

Why do you now have SUM around the output of the outer SELECT? Have you introduced a GROUP BY at this level too?

ROUND(SUM(Coordinate/Total*100.00),2)Coordinate,

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

methexis SSC Eights! Points: 815 More actions · Answer 6

All Done,

Stupid of me to sum and group again as it had already been done, results now set to decimals and I have got the data set I need.

What a relief!

Thanks again.