union query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Look at the following code:
SELECT LEFT(ZIP,3) AS ZIP3, COUNT (ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT( ZIP,3)
UNION SELECT LEFT(ZIP,5) AS ZIP5, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5)
HAVING COUNT(ZIP) >15;

Here is what my output looks like:
ZIPCODE COUNT
005 150
007 300
017 100
01710 50
018 19

I have two problems. The count for the 5-digit zip (50) is also included in
the count for the 3-digit zip (100). Also I need the 5-digits listed
together and the 3-digits listed together. I have been working on this for
several days. This is as close as I have come to having the right numbers.
 
It is not at all obvious to us, what you want your results to look like!
Please provide a sample output of your desired results.
 
This query is coming from another query that lists all 5-digit zipcodes and
their counts.
I need to get all the 5-digit codes that have a count over a certain number.
Of the ones that are left I need them in order by the first three digits.
ZIP COUNT
01756 100
01895 200
03679 167
etc...

018 95
020 56
079 20
306 99
etc.
 
Pardon me for adding some more to the solution, but you need a subquery to
eliminate the ones records you have counted in the Zip5 portion of the query.

Perhaps something like the following

SELECT LEFT(ZIP,3) AS ZIP3, COUNT (ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
WHERE LEFT(ZIP,5) NOT IN
(SELECT LEFT(ZIP,5)
FROM ZIPCODES
GROUP BY LEFT(ZIP,5)
HAVING COUNT(ZIP)>15)
GROUP BY LEFT(ZIP,3)
UNION
SELECT LEFT(ZIP,5) AS ZIP5, COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5)
HAVING COUNT(ZIPCODES)>15
ORDER BY Len(Zip3) Desc, ZIP3

That might be slow and if so there are probably a few ways to speed it up.
First, replace UNION with UNION ALL

Second, Add an identifier column to both sections of the union query and sort on
that vice sorting on the length of the zip3/5 field.

SELECT "3" as ZipLength, ...
UNION ALL
SELECT "5" ...
ORDER BY ZipLength DESC, ZIP3
 
I coped and pasted your code directly to my program. When I try to run the
query I get this error message:
The ORDER BY expression Len(ZIP3) includes fields that are not selected by
the query. Only those fields requested in the first query can be included in
an ORDER BY expression.
 
CHECK OUT THIS CODE:
THIS CODE MAKES ALL MY ZIPCODES 5 DIGITS(SOME HAVE THE +4 CODE ATTACHED) AND
COUNTS THEM.

SELECT LEFT(ZIP,5) AS UNIZIP, COUNT(ZIPCODES.ZIP) AS CTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5);

I HAVE CREATED ANOTHER QUERY TO SEPARATE THOSE THAT HAVE A COUNT OF LESS
THAN FIFTEEN TO BE LISTED BY THE FIRST THREE DIGITS, AND IF THEY HAVE A COUNT
OF MORE THAN 15 TO BE LISTED BY ALL FIVE DIGITS.
LOOK AT MY CODE BELOW.

SELECT LEFT(UNIZIP,3), CTOFZIP
FROM QRYUNIQUEZIP
WHERE UNIZIP NOT IN
(SELECT UNIZIP
FROM QRYUNIQUEZIP
WHERE CTOFZIP >15
GROUP BY UNIZIP)
GROUP BY LEFT(UNIZIP,3)
UNION
SELECT UNIZIP, CTOFZIP
FROM QRYUNIQUEZIP
WHERE CTOFZIP > 15
GROUP BY UNIZIP;

IT IS GROUPING THE ZIP CODE CORRECTLY IF I DONT HAVE THE "CTOFZIP" FIELD
INCLUDED IN THE SELECT STATEMENT. BUT i NEED THIS FIELD TO SHOW AND IM
GETTING THIS ERROR MESSAGE

YOU TRIED TO EXECUTE A QUERY THAT DOES NOT INCLUDE THE SPECIFIED _EXPRESSION
'CTOFZIP' AS PART OF AN AGGREGATE FUNCTION.
__________________________________________________
 
What happens if you drop the Order By statement? Does the query run and give
you results? If so, then we have isolated the problem.

Try the following variation.

SELECT "Z" as xOrder,
LEFT(ZIP,3) AS ZIP3,
COUNT (ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
WHERE LEFT(ZIP,5) NOT IN
(SELECT LEFT(ZIP,5)
FROM ZIPCODES
GROUP BY LEFT(ZIP,5)
HAVING COUNT(ZIP)>15)
GROUP BY LEFT(ZIP,3)
UNION
SELECT "A",
LEFT(ZIP,5),
COUNT(ZIPCODES.ZIP) AS COUNTOFZIP
FROM ZIPCODES
GROUP BY LEFT(ZIP,5)
HAVING COUNT(ZIPCODES)>15
ORDER BY xOrder, ZIP3
 
Back
Top