CODEWITHSUNDEEP
mysqlsql
BootstrapBill
BootstrapBill

Reputation: 143

Using a sum of values as a condition (SQL query)

I have a table that looks roughly like this

Year    Species   Count
1979    A         0
1980    A         10
1981    A         4
1982    A         3
1979    B         0
1980    B         1
1981    B         2
1982    B         3
1979    C         9
1980    C         14
1981    C         2
1982    C         1

What i want is to return all Year, Species, Count for those species that have a total count (as in summed over all years) of 10 or more. so for a total count of 20 i would want it to just return

1979    C         9
1980    C         14
1981    C         2
1982    C         1

i played around with having but havent really gotten anything useful (total SQL beginner)

Upvotes: 2

Views: 1562

Answers (3)

David Faber
David Faber

Reputation: 12486

You'll want to use GROUP BY with the SUM() aggregate function and HAVING clause (similar to WHERE, but for groups instead of rows), combined with a self-join:

SELECT t1.`Year`, t1.`Species`, t1.`Count`
  FROM mytable t1 INNER JOIN (
    SELECT `Species`, SUM(`Count`)
      FROM mytable
     GROUP BY `Species`
    HAVING SUM(`Count`) >= 20
) t2
    ON t1.`Species` = t2.`Species`

Upvotes: 0

Michael Humelsine
Michael Humelsine

Reputation: 601

This is the easiesy. You already have the counts. Group on species and filter table on the results of the subquesy. You can get the same functionality with an exists or a join also.

SELECT
    [YEAR]
    ,SPECIES
    ,[COUNT]
FROM TABLE
WHERE SPECIES IN (
    SELECT SPECIES
    FROM TABLE
    GROUP BY SPECIES
    HAVING SUM([COUNT]) > 20)
)

Adding some addtional explanation for BootstrapBill

Group by "makes multiple sets" for each unique value of the GROUP BY column. That allows the aggregate function SUM() act on only one set of the GROUP BY values at a time. HAVING is sort of like a WHERE clause for the GROUP BY statement that allows you to apply a predicate. The only fields allowed to be returned by a GROUP BY are the grouped column itself and the results of any aggregate function(s), you need to join back to or filter the original set to get the other columns your are targeting in the query.

And I apoligze, I did not see where the OP stated this was for MySql. The core concept is the same so I am leaving the answer. [] are MS SQL syntax for escaping the keywords COUNT and YEAR.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

In MySQL, you can do this using aggregation and a join:

select t.*
from table t join
     (select species, count(*) as cnt
      from table
      group by species
     ) s
     on t.species = s.species
where s.cnt >= 10;

Upvotes: 2

Related Questions