April 13, 2009 at 9:50 am
I have a table with Center, Sub_Center, City, State, Zip.
I run this query -
SELECT Center, Sub_Center, City, State, Zip, Count(Zip) AS ZipCount
FROM table
WHERE Center = '1' ANd Sub_Center = '2'
Group By Center, Sub_Center, City, State, Zip
Result - The sum of Count(Zip) is 2000.
If I just want to get the zipcount for 1000 centers, I still get the sum of Count(Zip) as 2000.
SELECT TOP(1000) Center, Sub_Center, City, State, Zip, Count(Zip) AS ZipCount
FROM table
WHERE Center = '1' ANd Sub_Center = '2'
Group By Center, Sub_Center, City, State, Zip
When I sum up the ZipCount field in excel to find out if I am getting the total zipcount as 1000, it still comes out to be 2000. Am I doing it right?
April 13, 2009 at 2:48 pm
TOP limits the number of rows returned by your query. Assuming there are less than 1000 rows returned by the first query without the TOP,then adding the TOP 1000 will not make a difference.
Try a sub-query:
SELECT Center, Sub_Center, City, State, Zip, Count(Zip) AS ZipCount
FROM
(
SELECT TOP 1000 Center, Sub_Center, City, State, Zip
FROM table
WHERE Center = '1' AND Sub_Center = '2'
ORDER BY something
) x
Group By Center, Sub_Center, City, State, Zip
--------------------
SELECT Center, Sub_Center, City, State, Zip, Count(Zip) AS ZipCount
FROM table
WHERE Center = '1' ANd Sub_Center = '2'
Group By Center, Sub_Center, City, State, Zip
Result - The sum of Count(Zip) is 2000.
If I just want to get the zipcount for 1000 centers, I still get the sum of Count(Zip) as 2000.
SELECT TOP(1000) Center, Sub_Center, City, State, Zip, Count(Zip) AS ZipCount
FROM table
WHERE Center = '1' ANd Sub_Center = '2'
Group By Center, Sub_Center, City, State, Zip
When I sum up the ZipCount field in excel to find out if I am getting the total zipcount as 1000, it still comes out to be 2000. Am I doing it right?[/quote]
John Deupree
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply