Search code examples
mysqlcountsubqueryrdbms

Count records with criteria

I use the following query to count the total player entries per tournament on a list.

    SELECT fab_tournaments.tournament, count(fab_plist.id) AS cnt
    FROM fab_plist
    INNER JOIN fab_tournaments ON fab_tournaments.id = fab_plist.tournament
    WHERE fab_tournaments.activation = 'No'
    GROUP BY fab_plist.tournament
    ORDER BY cnt DESC

So far so good and displays correctly the total player entries per tournament. The problem is that as the players are Men and Women I would like to count also in two different columns the total of the Men and the Women players per tournament.

I tried using subqueries but I get an error that the subquery return more than one rows.

Any ideas?

Thanks in advance!

Solution

  • If you're using MySQL, this will work.

    SELECT fab_plist.tournament,
       SUM(fab_plist.gender = 'male') AS men,
       SUM(fab_plist.gender = 'female') AS women,
       COUNT(*) AS total
FROM fab_plist
INNER JOIN fab_tournaments ON fab_tournament.id = fab_plist.tournament
WHERE fab_tournament.activation = 'No'
GROUP BY fab_plist.tournament

    If you're using some other database, you may have to change to

    SUM(CASE WHEN fab_plist.gender = 'male' THEN 1 END) AS men

    and similar for women.