Count up number of rows matching criteria in same table

Question

I'm trying to extract a list of results, with the greatet number of rows found. Basically, we have a table of values, linked with the key link_id_fk. What I want to basically do is look at each link_id_fk value, and then count the number of rows in the table for that record. So if I were to do it manually for each one, it would look like:

SELECT COUNT(*) FROM lsql_Flex_Values WHERE flex_type = "address" AND link_id_fk = 12345

I'm trying this query, but it always returns no results:

SELECT link_id_fk, 
    (SELECT COUNT(*) FROM lsql_Flex_Values WHERE flex_type = "address" AND link_id_fk = flex.link_id_fk) AS total 
    FROM lsql_Flex_Values flex WHERE flex_type = "address"

What am I doing wrong? I'm sure the veterans will spot it a mile off - but I'm just not seeing it :/

Answer 1

Using GROUP BY should avoid to use the subquery.

SELECT link_id_fk, 
       COUNT(*) AS total 
FROM   lsql_Flex_Values flex 
WHERE  flex_type = 'address'
GROUP BY link_id_fk;