Aggregate query results

[lrgclasper]

Greetings,

I have the following select statement from a single table ( Haul number, weight, species code).

I would like the output to provide me with hauls with only two species. When I use an OR clause it returns haul specific records for each species, some hauls have both species (which is what I want) and some hauls have only one species. So, basically I am looking to have an extrapolated weight for each haul having two different species. The species code is 5 and 10... For example, the output I want is haul 1, species 10, extrapolated weight is x amount; and for haul 1, species 5, extrapolated weight is x. I do not want records back that show hauls with only 1 species. For example, haul 1, species 5, but no species 10.

I cannot use the AND clause within species because each record is specific to one species. Any help would be appreciated! Thanks

[ronverdonk]

Welcome to The Scripts!

I have the following select statement from a single table ( Haul number, weight, species code).

Where is that SELECT statement you refer to? Show it.

Ronald :cool:

[vpmurdan]

you should be able to do with this:

select haul, count(*), sum(weight)
from table
 group by haul
 having count(*)=2;