query of a query

dodorisio · December 8th, 2003, 04:58 PM

Hello, i am stuck on a query and i have been searching non stop and cant seem to put my finger on what i need.

i have a view that is this

AcctNum Code Date Zipcode
1 32.2 010101 12345

i want to find out how many of those accounts are in each zipcode. there are 545 unique zips in the table. how would i take that list of zips and pipe it back through and get a count for each zip.

daniel

rgerald · December 8th, 2003, 05:05 PM

Try the following:

Code:

SELECT COUNT(AcctNum), ZIPCode FROM <view name> GROUP BY ZIPCode

You will need to substitute the name of the view for <view name>.

Rand

dodorisio · December 8th, 2003, 05:06 PM

thanks!!! gosh that is so embarrasing.. i need to get back into sql..thanks!!!

Quote:

quote:Originally posted by planoie
SELECT COUNT(Zipcode) FROM view GROUP BY Zipcode

Peter
------------------------------------------------------
Work smarter, not harder.

rgerald · December 8th, 2003, 05:14 PM

Even more readable as:

Code:

SELECT ZIPCode, COUNT(AcctNum) AS NumOfAccounts FROM <view name> GROUP BY ZIPCode

See - we are all in too much of a hurry!

Rand

rgerald · December 8th, 2003, 05:54 PM

Since you may only wish to see and count by the first five characters in the ZIPCode

SELECT LEFT(ZIPCode, 5) AS ZIP5, COUNT(AcctNum) AS NumOfAccts FROM <view name> GROUP BY LEFT(ZIPCode , 5) ORDER BY LEFT(ZIPCode, 5)

Rand

Jeff Mason · December 8th, 2003, 06:10 PM

Quote:

quote:SELECT LEFT(ZIPCode, 5) AS ZIP5, COUNT(AcctNum) AS NumOfAccts
FROM <view name> GROUP BY LEFT(ZIPCode , 5) ORDER BY LEFT(ZIPCode, 5)

For what it's worth, you don't need to repeat the expression in the ORDER BY clause; since you've given the column expression in the SELECT clause an alias, you can refer to it by the alias in the ORDER BY clause (note that you DO need to repeat it in the GROUP BY clause, though). Thus:

Code:

SELECT LEFT(ZIPCode, 5) AS ZIP5, COUNT(AcctNum) AS NumOfAccts
    FROM <view name> GROUP BY LEFT(ZIPCode , 5) ORDER BY ZIP5;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Ramanuj · December 9th, 2003, 02:41 AM

SELECT ZipCode, COUNT(*) FROM view GROUP BY Zipcode

rgerald · December 9th, 2003, 11:17 AM

Jeff:

It strikes me as inconsistent (and confusing) that it is acceptable to use the alias in the ORDER BY clause but that it is not acceptable to use the alias in the GROUP BY clause.

Rand

Jeff Mason · December 9th, 2003, 11:39 AM

Quote:

quote:It strikes me as inconsistent (and confusing) that it is acceptable to use the alias in the ORDER BY clause but that it is not acceptable to use the alias in the GROUP BY clause.

You won't get any argument from me about that. :)

The reason that it works that way is that column aliases don't come into scope until the resultset is constructed. This is right before the cursor that performs the ORDER BY clause is executed. The columns of the resultset are given their names either from their source as a 'table.column' reference or from the alias if it is provided. The columns as named are then passed to the cursor which performs the ordering operation. The output of the ordering is then returned to the client provider as the final resultset.

The execution of the FROM, WHERE, GROUP BY and HAVING clauses occur before the aliases come into scope, so you are forced to refer to the columns only by their 'table.column' syntax. This is also why you have to repeat any expressions (like 'LEFT(ZIPCode,5)') in those clauses.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com