Counting occurences of a key value 1

[WMitchellCPQ]

I have a table-

tblPlantedOn
SeedID | field2 | FarmID
12,1,4
12,2,4
1,2,4
1,4,5

I want to figure out which farms have only one type of seed planted on them.

The one thing that is causing me problems is that the two entries on farm 4 only count as one seed. This anyway is discounted here as another seed type - 1 is planted on farm 4. This one query is killing me

Any ideas folkes ?

 

[Jamfool]

This should do:

SELECT tblPlantedOn.FarmID
FROM tblPlantedOn
GROUP BY tblPlantedOn.FarmID
HAVING COUNT(*)=1;

 

[rac2]

The problem is that there is one type of seed planted on Farm No. 4, it happens to be planted in two fields.

You could define one query that has one row for each seed and farm combination, using a DISTINCT function or using a GROUP BY. Then use that as input to a GROUP BY farm with the HAVING COUNT(*) = 1 condition.

SELECT FarmID
FROM (
       SELECT DISTINCT SeedID,FarmID
       FROM tblPlantedOn
     ) 
GROUP BY FarmID
HAVING COUNT(*)=1

Kind of reminds me of my youth back in Illinois.

 

[dnoeth]

SELECT FarmID
FROM tblPlantedOn
GROUP BY FarmID
HAVING COUNT(DISTINCT SeedID)=1;

or

SELECT tblPlantedOn.FarmID
FROM tblPlantedOn
GROUP BY tblPlantedOn.FarmID
HAVING MIN(SeedID)=MAX(SeedID);

Dieter

 

[WMitchellCPQ]

Thanks guys..

 

[r937]

HAVING MIN(SeedID)=MAX(SeedID)

brilliant


;-)


rudy

http://r937.com/

SQL Consulting