October 20, 2009 at 11:15 am
I am trying to get the computed value for these columns in a single select statement. I have created this in vb.net(my original background) but would like to convert it to TSQL as to expand my knowledge.
SELECT TOP 100 PERCENT towergrid_reference_natowers.origin_latitude as [Latitude],
towergrid_reference_natowers.origin_longitude as [Longitude],
[Mile1] = Cast((SELECT COUNT(*) FROM towergrid_reference_natowers WHERE (towergrid_reference_natowers.distance_miles < 1)) as Float),
[Mile1 w/Pct] = Cast((SELECT COUNT(*) FROM towergrid_reference_natowers WHERE (towergrid_reference_natowers.distance_miles < 1)) as Float) * .98
FROM towergrid_reference_natowers
GROUP BY towergrid_reference_natowers.origin_latitude, towergrid_reference_natowers.origin_longitude
ORDER BY towergrid_reference_natowers.origin_longitude, towergrid_reference_natowers.origin_latitude
Thank you.
October 20, 2009 at 11:30 am
Now, I am not necessarily looking for the solution but maybe to a site that might have the answer even with some reading. (Trying to learn this, not get someone to write it for me.)
October 20, 2009 at 11:34 am
Hi,
Not really sure what you are after here!
Does the query you posted give the results you want?
Allister
October 20, 2009 at 11:38 am
No it gives a total of the whole table with the < 1, I am looking for the result for each individual lat, lon combination.
October 20, 2009 at 11:49 am
Does this give you what you need?
SELECT TOP 100 PERCENT
towergrid_reference_natowers.origin_latitude as [Latitude],
towergrid_reference_natowers.origin_longitude as [Longitude],
SUM(CASE WHEN towergrid_reference_natowers.distance_miles < 1 THEN 1 ELSE 0 END) AS [Mile1]
SUM(CASE WHEN towergrid_reference_natowers.distance_miles < 1 THEN 1 ELSE 0 END) * .98 AS [Mile1 w/Pct]
FROM towergrid_reference_natowers
GROUP BY towergrid_reference_natowers.origin_latitude, towergrid_reference_natowers.origin_longitude
ORDER BY towergrid_reference_natowers.origin_longitude, towergrid_reference_natowers.origin_latitude
Check out following reference for details of cross-tab queries http://www.sqlservercentral.com/articles/T-SQL/63681/
October 20, 2009 at 11:56 am
Very very nice, That's awesome. Not sure how I would ever come up with such a solution.
October 20, 2009 at 12:01 pm
I didn't either! follow the link and read (a lot;) and you'll get more tools in your bat-belt.
October 20, 2009 at 12:02 pm
I didn't realize that it would act almost like a cursor processing through the records like that, I must rethink my whole sql knowledge now.
October 20, 2009 at 12:05 pm
i think this is what you are looking for; you need to use a CASE to get your "conditional" count that you were trying to do in the internal WHERE statements:
==EDIT: Allister Reid beat me to it while i was checking synbtax! wtg!==
SELECT
towergrid_reference_natowers.origin_latitude as [Latitude],
towergrid_reference_natowers.origin_longitude as [Longitude],
SUM(CASE
WHEN towergrid_reference_natowers.distance_miles < 1
THEN 1
ELSE 0
END) AS Mile1],
SUM(CASE
WHEN towergrid_reference_natowers.distance_miles < 1
THEN 1
ELSE 0
END) AS Mile1] * .98 AS [Mile1 w/Pct],
FROM towergrid_reference_natowers
GROUP BY towergrid_reference_natowers.origin_latitude, towergrid_reference_natowers.origin_longitude
ORDER BY towergrid_reference_natowers.origin_longitude, towergrid_reference_natowers.origin_latitude
Lowell
October 20, 2009 at 12:12 pm
Thanks Lowell, you helped by helping me learn how to describe the problem as a "conditional count" thanks very much for that extra bit.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply