Hans Gerber Hans Gerber - 1 year ago 50
MySQL Question

Count all occurances of every value in a column

I hope my problem can be understood ...
How can I get the sum of rows for every value that can be found within a column for a certain foreign key?


enter image description here

Here I want to count the occurance of every value in the column "value" where the foreign key is 1.

So the example result would be like : 3 (3x "2") and 2 (2x "5")

Thanks in advance!

Answer Source

This is a very simple group by problem. There are a number of aggregation functions in MySQL and all other SQL dialects. Some vary from dialect to dialect so be sure to read the documentation to see if something exists to fit your use case.

In your particular case, you want to group by value and foreign key (e.g. value = 2 and foreign key = 1 is a group, value = 5 and foreign key = 1 is a different group), counting the values in each group:

  COUNT(value) AS count,
FROM my_table
GROUP BY value, foreign_key

Good luck!


I read from this portion of the original question:

3 (3x "2") and 2 (2x "5")

That the desired output would be 6 (3 x 2) and 10 (2 x 5). I see now the desired output was likely 3 (the count of 2's) and 2 (the count of 5's) in which case the correct query would be the edited one above.