I have one table where I would like to count the amount of names grouped by school, count whether school has one or two genders as well as count the specific occurence of a particular value in 3 different columns. I am able to do the counts in two different tables but I want to join them to make 1 table

These are the 2 separate queries I use successfully and create two different result sets

$sql = "SELECT COUNT(FName) as cnt, COUNT(DISTINCT(Gender)) as gnd, school, COUNT(*) FROM entries WHERE school <> 'Unattached' GROUP BY school";

$sql = "SELECT COUNT(Events   Events2   Events3) as events, school, COUNT(*) FROM entries WHERE (school <> 'Unattached') AND (Events = '100') OR (Events2 = '100') OR (Events3 = '100') GROUP BY school";
$result = $conn->query($sql);

I have tried using a union but it only gives me results for second query and the count of names is off.

$sql = "
SELECT COUNT(FName) as cnt, COUNT(Events   Events2   Events3) as events, COUNT(DISTINCT(Gender)) as gnd, school 
FROM entries WHERE (school <> 'Unattached')
UNION
SELECT COUNT(FName) as cnt, COUNT(Events   Events2   Events3) as events, COUNT(DISTINCT(Gender)) as gnd, school 
FROM entries WHERE (school <> 'Unattached') AND (Events = '100') OR (Events2 = '100') OR (Events3 = '100')
GROUP BY school";
$result = $conn->query($sql);

The left join gave me no results because I didn't know how to fit the 2 WHERE clauses.

How can I join the two queries to get one table with the following result

CodePudding user response：

Use SUM() to add up the number of rows where a condition is true. This works because a boolean value is 1 when it's true, 0 when it's false, so SUM() gets this count.

SELECT school, 
    COUNT(*) AS num_participants, 
    COUNT(DISTINCT gender) AS num_genders, 
    SUM(events = '100' OR events2 = '100' OR events3 = '100') AS num_100
FROM entries
WHERE school != 'Unattached'
GROUP BY school