Query quandry

[sqlcasey]

Hi,

Here's my deal. I have a customers table, and newly created table MSA data table. The MSA (metropolitan statistical area) table contains a lot of statistical information, including zip codes and for each zip code, a related MSA name. However, not all zip codes have a corresponding MSA name, that is some zip codes have a NULL for MSA name.

That said, I need a query that can associate the zip codes from the customers table to the MSA table, and create a grouping or count of customers per MSA NAME.

That is, select all zip codes from customers where the zip code matches the zip code in the MSA table where MSA name is NOT null.

So far the only way I've successfully done this is by creating an intermediary table with customer data and the MSA name.

That is, I don't know how to GROUP BY MSA name without the MSA name being in the same table as customers. Does that make sense.

So, I am wondering if there's a way I can get rid of the intermediary table altogether, in order to run this query.

Thanks much

 

[Catadmin]

What fields do you need from the customers table? Other than the Zip.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

This is the query I run, AFTER I create an intermediary table that has customer info and MSA Name in the same record.

select msaname, count(*) from customers_msa
where statusid > 30
and msaname is not null
group by msaname
order by msaname

I guess I just need a count for now.

Thanks

 

[gmmastros]

There are a couple things that come to mind.

Count will ignore nulls if you specify a column name.

select msaname, count(msaname) from customers_msa
where statusid > 30
group by msaname
order by msaname

or

select msaname, count(*) from customers_msa
where statusid > 30
group by msaname
Having msaname is not null
order by msaname

Give these a try. It may work. If it doesn't, I suggest you post some sample data and expected results.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

I'm probably missing something because I don't understand your need for an intermediary table when you could just join on ZipCode.

See if this works for you.

Select MSAName, Count(CustomerID)
from MSA 
join Customers 
on MSA.ZipCode = Customers.ZipCode
Group By MSAName

Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

Hi,

The problem lies in the fact that Customers and MSA have no related field except for zip code. Customer ID does not exist in the MSA table, nor does any information from the MSA table exist in the Customers table. That's way I created an intermediary table that links them by MSA Name.

The problem, as I see it is that I can't seem to write a query that will join the existing customers table to the MSA table:

eg)
SELECT m.MSAName, count(c.custid)
FROM customers c JOIN Reports.dbo.MSA m
ON (c.custzip = m.zipcode)
WHERE m.MSAName IS NOT NULL
AND c.statusid > 30
GROUP BY m.MSAName

Does not provide the correct result. It includes NULL MSA Names and the count of customers is about twice what it should be.

 

[Catadmin]

How are you linking the Customer records to MSA name outside of the Zipcode?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

I just created a view, that seemed to solve the problem.

Then I can group by on the view. The view consists of some customer fields, along with the MSA name from the MSA table, where zipcode = zipcode.

 

[okiiyama]

select custzip, count(*)
from customers
where custzip in (select zipcode from msa where msaname is not null)
and statusid > 30
group by custzip

 

[Catadmin]

So you're saying you've resolved your problem entirely then? Or was the view comment in response to my question?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[organic53]

Hi,

I am guessing there may be a problem with your MSA table. All MSA codes should (could) have a name. The MSA code is assigned to a Metropolitan area which may be comprised of many counties. The name is usually derived from the largest citie(s) or countie(s). The exception is the rural MSA, which is the bucket to catch all non-metro areas (every state, except Rhode Island and New Jersey) has a rural code. If the MSAs with NULL in the name begin with 9, then they are most likely rural MSAs and could easily be updated to "Alabama Rural", "Alaska Rural", etc...

If updating the MSA table is out of the question, rather than create an intermediate table or view, did you try something like (assuming you don't want to remove those without a MSAName):

SELECT COALESCE(MSA_NAME,'RURAL') AS 'MSA Name', a.ZIP, COUNT(*) AS 'Cust Count'
FROM Cust a
JOIN MSA b on a.ZIP=b.ZIP
GROUP BY COALESCE(MSA_NAME,'RURAL'), a.ZIP

 

[SQLSister]

Organic53, our MSA data has nulls as well and yes they are the smaller towns which are not part of a Metropolitan statistical area. Since I need these in my data, I use isnull and substitute the city, state name for the msa name which works nicely.

As far as not using them why not add a where clause, where MSAName is not null

Questions about posting. See faq183-874