Ad:

MS SQL Discussion Thread
Marked Solved
Views: 2882

Jun 22nd, 2007

Complex Query Help

Expand Post »

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

MS SQL Syntax (Toggle Plain Text)
SELECT SUBSTRING(Zip, 1, 3) FROM AvailHomeZips AS tZip
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:

MS SQL Syntax (Toggle Plain Text)
SELECT Zip FROM AvailHomeZips WHERE Zip LIKE @Zip + "%"
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

Similar Threads

pagination and search in PHP
Performance issue encountered need another approach in Oracle

MetalHobin

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Jan 2007

Permalink

Jun 23rd, 2007

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:

sql Syntax (Toggle Plain Text)
SELECT SUBSTRING(zip, 1, 3) as ZIP, sum(AvailNums) as SUM 
FROM AvailHomeZips GROUP BY ZIP;
select substring(zip, 1, 3) as ZIP, sum(AvailNums) as SUM 
from AvailHomeZips group by ZIP;

Last edited by ~s.o.s~; Jun 23rd, 2007 at 6:41 am.

~s.o.s~

Super Moderator

Featured Poster

Reputation Points: 3233

Solved Threads: 720

Failure as a human

Offline

8,872 posts
since Jun 2006

Permalink

Jun 25th, 2007

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.

MetalHobin

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Jan 2007

Permalink

Jun 25th, 2007

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:

sql Syntax (Toggle Plain Text)
SELECT SUBSTRING(zip, 1, 3) as Z, sum(AvailNums) as SUM FROM AvailHomeZips GROUP BY Z;
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.

~s.o.s~

Super Moderator

Featured Poster

Reputation Points: 3233

Solved Threads: 720

Failure as a human

Offline

8,872 posts
since Jun 2006

Permalink

Jun 25th, 2007

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.

MetalHobin

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Jan 2007

Permalink

Jun 25th, 2007

Re: Complex Query Help

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

sql Syntax (Toggle Plain Text)
SELECT SUBSTRING(zip, 1, 3), sum(AvailNums) FROM AvailHomeZips GROUP BY SUBSTRING(zip, 1, 3);
select substring(zip, 1, 3), sum(AvailNums) from AvailHomeZips group by substring(zip, 1, 3);

~s.o.s~

Super Moderator

Featured Poster

Reputation Points: 3233

Solved Threads: 720

Failure as a human

Offline

8,872 posts
since Jun 2006

Permalink

Jun 25th, 2007

Re: Complex Query Help

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

MetalHobin

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Jan 2007

Permalink

Jun 25th, 2007

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

~s.o.s~

Super Moderator

Featured Poster

Reputation Points: 3233

Solved Threads: 720

Failure as a human

Offline

8,872 posts
since Jun 2006

Permalink

Jun 25th, 2007

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

MetalHobin

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

9 posts
since Jan 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Seemingly Simple Issue

Next Thread in MS SQL Forum Timeline: Stored procedure with Dynamic columns?

DaniWeb Message

Cancel Changes

User Name
Password