@SempervivumDec 06.2020 — #I'm not a PHP nor SQL expert and I don't know about a pure-SQL solution, however when using PDO you can group the result as described here:
and then count the results like this: $res = $pdo->query('SELECT status FROM attendancetracking'); $result = $res->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC); var_dump($result); $count1 = count($result[1]); $count2 = count($result[2]); var_dump($count1, $count2); (I used some table I already had in my database, you need to replace the column name by "gender" and the table name).
@SempervivumDec 06.2020 — #PS: In the meantime I remembered that this can be done more easily by use of a group clause: $res = $pdo->query('SELECT status, COUNT(status) FROM attendancetracking GROUP BY status'); $result = $res->fetchAll(); var_dump($result); $count1 = $result[0][1]; $count2 = $result[1][1]; var_dump($count1, $count2);
@NogDogDec 06.2020 — #While I can imagine some interesting(?) ways to do it with multiple joins on the same table, I think @sibert's approach is probably the way to go. Fleshing it out a bit for PDO, maybe: <i> </i>$sql = " SELECT (SELECT COUNT(id) FROM the_table WHERE some_id = :some_id AND gender = 1) AS male, (SELECT COUNT(id) FROM the_table WHERE some_id = :some_id AND gender = 2) AS female "; $stmt = $pdo->prepare($sql); $stmt->execute([':some_id' => $some_id]);
@SempervivumDec 06.2020 — #@NogDog#1625658 Why do you favor sibert's solution over my second one? It needs **two** subqueries why mine does the job by one.
@NogDogDec 06.2020 — #@Sempervivum#1625661 I think the only reason is that it gets both values in one result row. It's a trivial preference, and I wouldn't lose any sleep if the OP went with your solution. :) I just kind of like letting the DB do the work when it can (and it's not too complex/weird/inefficient).