Hans Gerber - 10 months ago 42

MySQL Question

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?

Example:

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

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:

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

Good luck!

**EDIT:**

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.

Source (Stackoverflow)