November 8, 2011 at 4:58 am
Thanks for all the help on the previous post, and I am stuck again!
The code below counts and sums up the number of instances, the next step I would like the number to actually be a %, I have given it a shot but its not working out.
Below is the code that works:
Select
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
Order by CONTRY_NAME_LC
Now I need to take the 'Total' figure and include it within the SUM statements to be left with a percentage, something like this:
SUM((case when GeoResolutionCode = 1 then 1 else 0 end)/(Total*100)) Coordinate,
I tried to define the 'Total' seperatly from the main select statement like this so total would be A.Total but that did not work :crying:
with A as
(
select
CONTRY_NAME_LC,
COUNT (GeoResolutionCode) as Total
From EM_REFDATA.dbo.Cargo_Mapping
Group by CONTRY_NAME_LC
)
Any ideas and pointers would be very much appreciated!
Many Thanks
November 8, 2011 at 5:12 am
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.
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
November 8, 2011 at 5:29 am
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!
November 8, 2011 at 5:35 am
Looks about right.
Try
SUM(Coordinate/Total*100.00) Coordinate
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
November 8, 2011 at 5:49 am
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:
November 8, 2011 at 5:59 am
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,
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
November 8, 2011 at 6:18 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply