-1

I am so totally lost.

Working on a database for my co-rec team. Players, Matches, Available players for a match, chosen players for a match, etc.....

The first major step I'd like is to be able to combine Players, Matches and Available to get a list of matches with the number of Women and number of Men available.

Here are my tables:

Players (id, Gender, Name, ....)

id    Gender  Name
 1    M       David
 2    M       Alberto
 3    F       Alison
 4    F       Karen
 5    F       Callie
 6    M       Stephan

Matches (id, ...)

 id     
  1   
  2
  3

Available (id, matchID, playerID)

 id   matchID  PlayerID
  1     1         1
  2     1         8
  3     1        11
 ...   ...       ...
  16    2         1
  17    2         2
  18    2        15
 ...   ...       ...
  26    3         6
  27    3         7
  28    3        18

Desired Result

Match     Women     Men     Total
  1         5       10        15
  2         4        6        10
  3         6       10        16
 ...       ...     ...       ...

Here's the closest I've got (just this morning):

select m.id, p.gender
  from matches m
  inner join available a on m.id = a.matchid
  inner join players p on p.id = a.playerid
Zonker.in.Geneva
  • 1,389
  • 11
  • 19
  • 1
    I think you should still post your code so far. Learning why your code doesn't work is much better than someone telling you what will work. – kaineub Aug 10 '17 at 22:33
  • This question should be helpful for getting the different counts: https://stackoverflow.com/questions/33139000/multiple-query-same-table-but-in-different-columns-mysql/33139182#33139182 – Barmar Aug 10 '17 at 22:43
  • This should be helpful for aggregating when you join multiple tables: https://stackoverflow.com/questions/37199736/a-count-for-each-join-optimisation – Barmar Aug 10 '17 at 22:44
  • @kaineub i'm literally just flailing about at this point. – Zonker.in.Geneva Aug 10 '17 at 22:44
  • Unless you're after the psychic forum, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Aug 10 '17 at 23:32
  • You may be "flailing" but we are not mind readers. You can see data but **we cannot**. You have **alluded** to what your tables look like, but it **lacks detail** and there is no data given. In addition - truly - we need to see that you have at least tried, so please add the query you used even if it isn't right. – Paul Maxwell Aug 11 '17 at 03:21
  • I was trying to stick to the essentials, not flood the question with extra nonsense, but ok, post edited to add more info..... – Zonker.in.Geneva Aug 11 '17 at 06:27

1 Answers1

0

Morning clarity:

select m.id,
  sum(case when p.gender="Male" then 1 else 0 end) "Males",
  sum(case when p.gender="Female" then 1 else 0 end) "Females",
  count(p.gender) "Total"
from matches m
inner join available a on m.id = a.matchid
inner join players p on p.id = a.playerid
group by m.id
Zonker.in.Geneva
  • 1,389
  • 11
  • 19