Hans Gerber - 1 year ago 71

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!

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

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:

```
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.

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**