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 :/