Left join same table with count specific value

Question

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

FName	School	Gender	Events	Events2	Events3
Ann	Marymount	F	HJ	LJ	TJ
Peter	Marymount	M	100	200	400
Drew	St Hughs	M	100	200
Davis	St Hughs	M	200
Kat	Campion	F	400
Molly	Campion	F	400
Mike	Marymount	M	800
Fran	Campion	M	100	200

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

School	# of Participants	# Genders	# 100
Marymount	3	2	1
St Hughs	2	1	1
Campion	3	2	1

What is COUNT(Events + Events + Events3) supposed to count? COUNT(<expression>) counts the number of rows where the expression is not NULL. — Barmar
– Barmar, Commented Jan 17, 2023 at 21:19
Content relating to "Conditional Aggregation": SQL GROUP BY and COUNT and conditional SUM with column value and GROUP BY with COUNT condition and Conditional sum in Group By query MSSQL and MySQL query group by and count on multiple conditions and how to SUM a column by different condition then group by date — mickmackusa
– mickmackusa ♦, Commented Jan 17, 2023 at 23:00

Barmar · Accepted Answer · 2023-01-17 21:21:47Z

0

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

answered Jan 17, 2023 at 21:21

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mickmackusa Over a year ago

Does it also work to use SUM('100' IN (events, events2, events3))? I didn't test this, but I feel like I've done this before.

Barmar Over a year ago

@mickmackusa Yes, that should work as well. I've used that in code before.

Collectives™ on Stack Overflow

Left join same table with count specific value

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related