mysql group by and count rows where multiple columns all match [duplicate]

Question

From a single table called 'student', I have an output like:

Country	Subject	Level
France	History	1
France	Art	1
UK	History	2
France	History	1

If all three columns match, I want to group them and count how many instances, so the above becomes:

Country	Subject	Level	Count
France	History	1	2
France	Art	1	1
UK	History	2	1

I could probably get the answer by putting the existing result in some mad PHP array, but I'd rather learn how to achieve it in MYSQL. Any tips on how to group by and count rows where multiple columns all match would be much appreciated. Thank you.

Amit Mohanty · Accepted Answer · 2023-11-10 10:09:20Z

0

By using the GROUP BY clause along with the COUNT function, you can get your result.

SELECT Country, Subject, Level, COUNT(*) AS Count
FROM student
GROUP BY Country, Subject, Level;

answered Nov 10, 2023 at 10:09

Amit Mohanty

1,8882 gold badges15 silver badges23 bronze badges

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

1 Comment

user3541184 Over a year ago

Thank you, Amit! That works perfectly. For some reason I imagined it would be more complicated. Thanks for teaching me.

Collectives™ on Stack Overflow

mysql group by and count rows where multiple columns all match [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related