November 2, 2011 at 11:03 am
Hi Guys,
I know there is a way to do this, I have done similar things with nested queries but I just cant get this to work.
I want to work out what percentage of the values are less than or equal to six in the same column.
This gives me the total per country:
Select CountryCode, COUNT (GeoResolutionCode) as Total
From EM_REFDATA.dbo.Cargo_Mapping
Group by CountryCode
and this the number = or less than 6
Select CountryCode,
COUNT (GeoResolutionCode)
From Cargo_Mapping
Where GeoResolutionCode <='6'
Group By CountryCode
So I need to take the results from query number 1 and query number 2 and calculate the percentage that result 2 is of result 1?
I thought I could give each query a name such as A and B and then perform a 3rd query having defined them but it didnt work, any help or pointers would be fantastic!
😀
November 2, 2011 at 11:18 am
You are apparently using SQL Server 2008 based on the forum you posted. Put the two queries into CTE's and then use those CTE's in the final query.
Here is a skeleton to start with:
with A as (
),
B as (
)
select ...
November 2, 2011 at 11:24 am
Yep 2008,
I will give this a shot and let you know how it goes!
Thanks
November 2, 2011 at 11:32 am
use EM_REFDATA;
with A as
(
Select CountryCode, COUNT (GeoResolutionCode) as Total
From EM_REFDATA.dbo.Cargo_Mapping
Group by CountryCode
),
B as
(
Select CountryCode,
COUNT (GeoResolutionCode) as Geo
From Cargo_Mapping
Where GeoResolutionCode <='6'
Group By CountryCode
)
Select CountryCode, SUM(B.Geo/A.Total) as 'Percentage'
Group by CountryCode
Does the final select statement need to have a FROM clause in it and point at A and B somehow, as A.Total and B.Geo are not recognised?
:ermm:
November 2, 2011 at 11:36 am
Yes, you do need a "FROM"-clause. The CTE's can be used as tables in your final query.
November 2, 2011 at 11:39 am
Martin Schoombee (11/2/2011)
Yes, you do need a "FROM"-clause. The CTE's can be used as tables in your final query.
Yes, and some how they need to link together unless a cartesian product is oaky.
November 2, 2011 at 12:08 pm
Okay getting there slowly on this one....
use EM_REFDATA;
with A as
(
Select CountryCode, COUNT (GeoResolutionCode) as Total
From EM_REFDATA.dbo.Cargo_Mapping
Group by CountryCode
),
B as
(
Select CountryCode,
COUNT (GeoResolutionCode) as Geo
From Cargo_Mapping
Where GeoResolutionCode <='6'
Group By CountryCode
)
Select a.CountryCode, SUM(B.Geo/A.Total)
From A,B
Group By A.CountryCode
Order by CountryCode
This is now producing results, just not the right ones, for example
the values would be b.75/a.255 = 0.29 for some reason I am getting 508!! Am I going mad but I though that this expression 'B.Geo/A.Total' would do the same thing?!
Thanks again for talking me through this, much better way to learn!
November 2, 2011 at 12:21 pm
You need to add join criteria. so your query would be something like
...
FROM A INNER JOIN
B ON A.ID = B.ID
WHERE...
November 2, 2011 at 12:55 pm
venoym (11/2/2011)
You need to add join criteria. so your query would be something like
...
FROM A INNER JOIN
B ON A.ID = B.ID
WHERE...
Sounds like an echo. I seem to remember mentioning that as well, just didn't show it explicitly like you.
😉
November 3, 2011 at 3:59 am
Hi Guys,
Just want to say thanks for all your help with this, this is the final working code
use EM_REFDATA;
with A as
(
Select CountryCode, CAST(COUNT (GeoResolutionCode) AS FLOAT) as Total
From EM_REFDATA.dbo.Cargo_Mapping
Group by CountryCode
),
B as
(
Select CountryCode,
CAST(COUNT (GeoResolutionCode)AS FLOAT) as Geo
From Cargo_Mapping
Where GeoResolutionCode <='6'
Group By CountryCode
)
Select ISNULL(A.CountryCode,B.CountryCode) CountryCode,
ROUND(CAST (B.Geo/A.Total*100 AS FLOAT),2) as Percentage
From A FULL OUTER JOIN B
ON A.CountryCode = B.CountryCode
Order by a.CountryCode
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply