CODEWITHSUNDEEP
mysqlsqlcountgroup-bymysql-5.5
prograhammer
prograhammer

Reputation: 20630

How to query for a count within a count (without using a sub query)?

If you study this table for a moment, you will see Dave is unique because he has won multiple awards in a single year and he has done this for multiple years.

person  award   year
-------------------------------
Dave    red     2015
Dave    blue    2015
Dave    red     2013
Dave    green   2013
Susan   blue    2015
Susan   green   2011
Susan   red     2011
Susan   red     2010
Tom     red     2012
Tom     blue    2012
Tom     green   2012
Tom     yellow  2012
Tom     purple  2012

Is there a way to query for Dave (multi-award winner for more than 1 year) without using a subquery?
For example, you could say GROUP BY person HAVING COUNT(DISTINCT year) > 1 AND COUNT(*) > 3 but this would also yield Susan.

Upvotes: 2

Views: 68

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

I think you could use this:

select a1.person
  from awards a1
  join awards a2
    on a1.person = a2.person
   and a1.year = a2.year
   and a1.award <> a2.award
 group by a1.person
having count(distinct a1.year) > 1

Fiddle: http://sqlfiddle.com/#!2/b98bf/8/0

But you would be better off with a subquery:

select person
  from (select person, year, count(*) as num_in_yr
          from awards
         group by person, year) x
 group by person
having sum(num_in_yr >= 2) >= 2

Fiddle: http://sqlfiddle.com/#!2/b98bf/7/0

Upvotes: 3

Related Questions