Query to exclude dupes 1

[vladk]

Hi,

There is a table TABLE1 and the fields: ZIP, TERR, VAL:

ZIP TERR VAL
0001 01 10
0001 01 12
0001 01 14
0002 01 17
0002 01 21
0002 02 24
....

I need to roll them up by ZIP and TERR and present two sets of data: where ZIP has one only TERR and ZIP has more than one TERR.

I wrote this select that does half of the job: it rolls things up.

SELECT ZIP,
TERR,
SUM(VAL) AS TOTAL_VAL
FROM TABLE1
GROUP BY ZIP,
TERR
ORDER BY ZIP,
TERR;

I am not sure how to divorce results for ZIP 0001 and 0002 (they have different number of TERR - 1 and 2). I would prefer two separate queries for them.

Can anybody help?

Thanks!

vladk

 

[rjoubert]

SELECT ZIP,
TERR,
SUM(VAL) AS TOTAL_VAL
FROM TABLE1
GROUP BY ZIP,
TERR
HAVING ((ZIP) = "0001")
ORDER BY ZIP,
TERR;

 

[vladk]

rjoubert,

Thank you for the responce. I need separate ZIPs not by the name but by the number of TERRs they belong to.

vladk

 

[rjoubert]

Not sure if I understand completely, but I'll give it a shot...

replace HAVING ((ZIP) = "0001") with HAVING ((TERR) = "01")

 

[vladk]

rjoubert,

Thank you for the responce again.
I need separate ZIPs not by the names of TERRs or ZIPs but by the number of TERRs ZIPs belong to.
For example, ZIP 0001 belongs to one TERR while ZIP 0002 belongs to two different TERRs and I want to separate them in the output based on this difference in the count of TERRs per a ZIP.

vladk

 

[rjoubert]

So you need one query that shows the ZIPs that have one TERR, a 2nd query that shows ZIPs that have two TERRs, etc.? Where do you stop? How would you know the Max number of TERRs that can be associated with a ZIP?

 

[vladk]

rjoubert,

I just need two queries: one for one TERR per ZIP and second for the rest.

vladk

 

[rjoubert]

First query...

SELECT ZIP,
TERR,
SUM(VAL) AS TOTAL_VAL,
COUNT(ZIP) AS ZIPCOUNT
FROM TABLE1
GROUP BY ZIP,
TERR
HAVING (COUNT(ZIP) = 1)
ORDER BY ZIP,
TERR;

Second query...

SELECT ZIP,
TERR,
SUM(VAL) AS TOTAL_VAL,
COUNT(ZIP) AS ZIPCOUNT
FROM TABLE1
GROUP BY ZIP,
TERR
HAVING (COUNT(ZIP) > 1)
ORDER BY ZIP,
TERR;

 

[vladk]

rjoubert,

Thank you for the responce again.

Unfortunately, your queries do not produce right count. If ZIP 0001 listed 200 times for the same TERR then it will not be in your first query output even if belongs to one only TERR.

vladk

 

[PHV]

SELECT A.Zip, A.Terr, Sum([Val]) AS Total_Val
FROM Table1 AS A
WHERE (SELECT Count(*) FROM (SELECT DISTINCT Zip,Terr FROM Table1 WHERE Zip=A.Zip))=1
GROUP BY A.Zip, A.Terr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vladk]

PHV,

Thank you for the SELECT. I tried it and got the input box to enter parameter value for A.Zip. I think, "A" is not recognized at third level.

vladk

 

[PHV]

So, create a query named, say, qryCountDistinct:
SELECT Zip, Count(*) AS CountDistinct
FROM (SELECT DISTINCT Zip, Terr FROM Table1) AS A
GROUP BY Zip

Then:
SELECT A.Zip, A.Terr, Sum(A.Val) AS Total_Val, B.CountDistinct
FROM Table1 AS A INNER JOIN qryCountDistinct AS B ON A.Zip = B.Zip
GROUP BY A.Zip, A.Terr, B.CountDistinct

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vladk]

PHV,
Outstanding!