Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fujin
Starting Member
2 Posts |
 Posted - 2003-05-20 : 16:57:20
|
| First off, I've found this forum (and website) to be incredibly helpful. "Thank you" to everyone here.I apologize if a question of this sort has already been asked, but here goes:We're trying to create a report that lists our total sales by county.We have two tables:The SALES table lists each sale. Each record contains a zip code (int) and a sale total (money).The ZIP table associates zip codes with counties. Each record contains a zip code (int) and the county that zip code is a part of (varchar).Our problem stems from the fact that sometimes a single zip code will be part of multiple counties, in which case there are multiple rows in ZIP. One (and only one) of these rows will have a "main" flag set (bit). The main flag indicates that this is the county we want to use for that zip code.Keep in mind that zip codes which appear only once in ZIP do not have the main flag set. The main flag is only set when there are multiple rows sharing a single zip, and in those cases it is only set for one of those rows.What I need is a SQL statement that will calculate the total sales for each county, making sure to select only the "main" county when there are multiple possibilities.For example, here is some sample data:ZIP:zip - county - main 92100 - ORANGE - NULL90100 - LOS ANGELES - 190100 - ORANGE - NULLSALES:zip - total 92100 - 5.000092100 - 10.000092100 - 15.000092100 - 20.000090100 - 1.000090100 - 2.000090100 - 3.000090100 - 4.0000There are two counties associated with zip code 90100, but only one of them (LOS ANGELES) should be used for this report because its main flag is set.My report should show an ORANGE total of 50 and a LOS ANGELES total of 10.If I can't get a query to do this, I'm just going to create a new table with a one-to-one mapping between zip codes and counties and write a cumbersome query to populate it from my existing data. Then I'll use this new table for the reports. I'd like to avoid that if possible, though.I should also mention that the query can't be too complex, because it needs to execute relatively quickly. I'd like to avoid nasty subqueries and things of that nature if I can.Any help would be greatly appreciated! I'm really at a loss with this one. Thanks. |
|
|
allyanne
Starting Member
18 Posts |
Posted - 2003-05-20 : 17:25:00
|
| What's your beef with "nasty subqueries and things of that nature"? You can't do what you want without them. If you're worried about speed, look into creating some indexes.Try this:select dt.county, sum(total) as totalfrom SALES as sjoin ( select zip, county from ZIP where main = 1 or zip in ( select zip from ZIP group by zip having count(zip) = 1 )) as dt on s.zip = dt.zipgroup by dt.county |
 |
|
|
fujin
Starting Member
2 Posts |
Posted - 2003-05-20 : 17:54:52
|
| Your code was just the trick. Thank you very much! :) |
|
|
|
|
|
|
|
|
Difficult join?