1

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

2

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;
  • Ah man - I feel stupid now. It was because I had address and not addresses as the flex_type value!!!! If fixed that, and my original query worked. However, yours is nicer (and stops repetitive values), and is faster. I ended up with: SELECT link_id_fk, COUNT(*) AS total FROM lsql_Flex_Values flex WHERE flex_type = 'addresses' GROUP BY link_id_fk ORDER BY total DESC; – Andrew Newby Jan 4 '18 at 8:49
  • I'm glad to help. – McNets Jan 4 '18 at 8:50
  • For better performance (if the table is large): INDEX(flex_type, link_id_fk) in this order. – Rick James Jan 4 '18 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.