CODEWITHSUNDEEP
mysql
George Marshall
George Marshall

Reputation: 59

mysql - select groups of rows by count?

Given a table showing awards earned by members of an organization, I am trying to list all the members who earned three or more awards after a given date, and to list all of the awards earned by those members. I have a query that will correctly list the members, but it shows only one row per member, and I'm trying to see all of their awards.

There is one row in the table for each award ("Award") earned by any member, with the award type and the date it was earned ("Awarddate").

Here's what I have:

SELECT Membername, Award, Awarddate,COUNT(Membername) 
FROM awards 
WHERE Awarddate > '2011-06-30' 
GROUP BY Membername HAVING COUNT(Membername) > 2

Suggestions?

Upvotes: 1

Views: 111

Answers (2)

piotrm
piotrm

Reputation: 12356

SELECT a.Membername, a.Award, a.Awarddate, Awardcount.Awards
FROM awards a
JOIN
  ( SELECT Membername, COUNT(*) AS Awards
    FROM awards
    WHERE Awarddate > '2011-06-30' 
    GROUP BY Membername 
    HAVING Awards > 2 ) Awardcount
ON a.Membername = Awardcount.Membername

Edit: to list only awards earned after given date add one more line to this query as suggested by ypercube:

WHERE a.Awarddate > '2011-06-30'

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You could use GROUP_CONCAT() aggregate function:

SELECT Membername
     , COUNT(*)                                    AS NumberOfAwards
     , GROUP_CONCAT(Award ORDER BY Awarddate)      AS Awards
     , GROUP_CONCAT(Awarddate ORDER BY Awarddate)  AS AwardDates
FROM awards 
WHERE Awarddate > '2011-06-30' 
GROUP BY Membername 
HAVING COUNT(*) > 2

Upvotes: 1

Related Questions