Complex Query Help
 

I need to write a query that pulls out Zip codes by the first three characters, so I started with this:

SELECT SUBSTRING(Zip, 1, 3) FROM AvailHomeZips AS tZip

But I would also like to query out the zips by those substrings I pulled out, so that would be like this:

SELECT Zip From AvailHomeZips WHERE Zip LIKE @Zip + "%"

And I need the result to be unique, and also I would like to sum all of the values that are in a column called [AvailNums] which belong to zip codes that have similar beginnings.
In other words if there are four zip codes like so:
Zip AvailNums
97301 425
97305 345
12302 1236
12305 445

I would like the resultset to look like this:
Zip AvailNums
973 770
123 1681

Re: Complex Query Help
 

I don't know how exactly MS SQL behaves, but here is an equivalen in MySQL. Try to modify this to your needs:

select substring(zip, 1, 3) as ZIP, sum(AvailNums) as SUM 

from AvailHomeZips group by ZIP;

Re: Complex Query Help
 

That worked, except for the Zip column, I am still getting redundant zips. For all of the zips with the same first three letters, I would like to group those together.
I can't find a syntactically correct way of incorporating DISTINCT into this query.

Re: Complex Query Help
 

I had tried the query pasted by me in my previous post on MySQL so I don't know what went wrong when the same was ported to MS SQL.

Try this:

select substring(zip, 1, 3) as Z, sum(AvailNums) as SUM from AvailHomeZips group by Z;

I guess MS SQL doesn't take into consideration the case hence the results. Post if it works or not.

Re: Complex Query Help
 

No, this didn't work, because when I use the AS keyword in MS SQL, I can't use the alias in a GROUP BY clause, it give me a syntax error.

I just got a SQL 2005 Programming book, but I appreciate the help, I will keep looking and post If I find a satisfactory solution.

Re: Complex Query Help
 

If what you say is true, then try out this one:

select substring(zip, 1, 3), sum(AvailNums) from AvailHomeZips group by substring(zip, 1, 3);

Re: Complex Query Help
 

That did it! It was the GROUP BY SUBSTRING() Combination, thank you.
Marked as Solved

Re: Complex Query Help
 

Glad it worked out for you, though I must go out to say that MS SQL sucks if it can't even allow a alias to be used with the group-by clause which is so common a thing with present databases. So much for the Enterprise level database... ;-)

Re: Complex Query Help
 

I know, I should just use MySQL, the only thing is that the MySQL team hasn't released the MySQL-Visual Studio integration tools yet.
Until then, Adios