Counting distinct values for multiple months

[ProInfo]

Hi guys,
Got a little problem here. I can't for the life of me, figure out how to do this.
pid | firstlast | lastvisit | zip
---------------------------------------
435 | 2001-01-17 | 2012-01-21 | 46530
567 | 2001-01-18 | 2012-01-21 | 46530
532 | 2001-01-19 | 2012-01-22 | 46535
536 | 2001-01-19 | 2012-01-23 | 46535
539 | 2001-01-20 | 2012-01-27 | 46521

Here is my SQL query:
SELECT DISTINCT zip, COUNT(zip) AS totalzip FROM production WHERE MONTH(lastvisit) = "1" GROUP BY zip ORDER BY totalzip DESC;
Output:

zip | totalzip
---------------------
46530 | 2
46535 | 2
46521 | 1


This is great for the 1st month, but I need this for the entire year. I could run this query 12 times, however 2 problems occur. I have over 300 zip codes for the entire year. On some months the zip code is not present, so the count is 0 (but the SQL output doesn't output the "zero data". Also, when I order by totalzip, the order changes from month to month, and this does not allow me to paste them into a spread sheet. I can order by zip code, but again the "zero" data zipcodes are not present and so the list changes from month to month.

Any thoughts or suggestions would be much appreciated!

Thanks so much!

 

[r937]

SELECT zip
     , MONTH(lastvisit) AS lastmonth
     , COUNT(*) AS totalzip 
  FROM ( SELECT DISTINCT zip
           FROM production ) AS zips
LEFT OUTER
  JOIN production 
    ON production.zip = zips.zip
GROUP 
    BY zips.zip 
ORDER 
    BY lastmonth ASC
     , totalzip DESC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ProInfo]

I am getting an error that says "Column 'zip' in the field list is ambiguous.

Also, How can you SELECT zip, MONTH (lastvisit), COUNT(*) FROM ("SELECT DISTINCT zip FROM production") when this sub query only returns a list of zip codes and no lastvisit information.

Thanks for trying. I have posted this in a couple of forums and nobody seems to have the right answer.

 

[r937]

regarding the ambiguous column in the SELECT clause, that's my fault, it should be zips.zip

but as to your other issue (the subquery returns only a list of zip codes)...

... this is entirely correct

do you know how a LEFT OUTER JOIN works?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ProInfo]

Hi there,

I know some about outer joins, and can see how they work when given a small example. But to actually use them (and think of them on my own is rather difficult). I've mostly had to use basic SQL when making websites, and smaller web based programs. However, now that I am getting into data manipulation and data mining.... its going to come in handy more often.

I was able to get a working query. However, I think there might be a problem with it.

I did a basic query:
SELECT * FROM production WHERE MONTH( lastvisit ) = "1" AND zip = "49120"
(I received 78 rows from the result)

However, using your query it came back with 1084. Which is the same number I get when I run this query:
SELECT * FROM production WHERE zip = "49120"

Your query comes back with:

zip| lastmonth | totalzip
--------------------------
49120 | 1 | 1084


The correct answer should be
zip| lastmonth | totalzip
--------------------------
49120 | 1 | 78


Any thoughts?

 

[r937]

what do you get when you run this query --

SELECT DISTINCT zip FROM production

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ProInfo]

372 unique zipcodes

 

[r937]

good

and how many rows do you get for this query --

SELECT DISTINCT zip, MONTH(lastvisit)
  FROM production

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ProInfo]

I get 1328 rows.

 

[r937]

thank you

in that case, i am quite confident that the query i wrote for you will produce exactly that same number of rows

if you get something different, it's because you changed what i wrote

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon