Select Count as Column

Question

Select Count as Column

Jonathan Turlington

Old Hand

Points: 360
More actions
October 20, 2009 at 11:15 am

#75530

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.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jonathan Turlington Old Hand Points: 360 More actions · Answer 1

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.)

Allister Reid SSCrazy Points: 2666 More actions · Answer 2

Hi,

Not really sure what you are after here!

Does the query you posted give the results you want?

Allister

Jonathan Turlington Old Hand Points: 360 More actions · Answer 3

No it gives a total of the whole table with the < 1, I am looking for the result for each individual lat, lon combination.

Allister Reid SSCrazy Points: 2666 More actions · Answer 4

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/

Jonathan Turlington Old Hand Points: 360 More actions · Answer 5

Very very nice, That's awesome. Not sure how I would ever come up with such a solution.

Allister Reid SSCrazy Points: 2666 More actions · Answer 6

I didn't either! follow the link and read (a lot;) and you'll get more tools in your bat-belt.

Jonathan Turlington Old Hand Points: 360 More actions · Answer 7

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.

Lowell SSC Guru Points: 323518 More actions · Answer 8

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jonathan Turlington Old Hand Points: 360 More actions · Answer 9

Thanks Lowell, you helped by helping me learn how to describe the problem as a "conditional count" thanks very much for that extra bit.

Select Count as Column

Cookies on SQLServerCentral