CODEWITHSUNDEEP
mysqlcount
MacMac
MacMac

Reputation: 35321

MAX() on COUNT() subquery that returns multiple rows

I have a query that gets the number of rows returned within selecting records, my query looks like this:

SELECT rec.record_id,
       rec.record_title,
       usr.user_id,
       usr.username,
       (
           SELECT COUNT(DISTINCT rec.record_id)
           FROM records rec
           WHERE rec.record_title LIKE '%random%'
           GROUP BY rec.record_id
       ) AS total_records
FROM 
(
    records rec

    INNER JOIN members usr
)
WHERE rec.record_title LIKE '%random%'
GROUP BY rec.record_id
LIMIT 0, 25

I need to be able to return the number of records in the total_records subquery using the maximum number found when it includes GROUP BY and has multiple records returned which I only need one and that should be the number of records found within the subquery.

How can you get the total records in the subquery by finding the maximum number found or add the multiple records together to make a number of records found.

Upvotes: 0

Views: 3077

Answers (2)

Jody
Jody

Reputation: 8291

I think I need some clarification... maximum number of what? "using the maximum number found"

I generally shy away from putting subqueries in the select statement. I'd probably restructure yours like this (pretty sure it's the same) See if thinking of it this way helps?

SELECT rec.record_id, rec.record_title, usr.user_id, usr.username
FROM ( records rec

INNER JOIN members usr
INNER JOIN  (
       SELECT COUNT(DISTINCT rec.record_id), rec.record_id as record_id
       FROM records rec
       WHERE rec.record_title LIKE '%random%'
       GROUP BY rec.record_id
   ) AS total_records on rec.record_id = total_records.record_id

) WHERE rec.record_title LIKE '%random%' GROUP BY rec.record_id LIMIT 0, 25

Upvotes: 1

Jonathan Hall
Jonathan Hall

Reputation: 79614

Maybe this is what you want? If not, please clarify your question, and I'll try again.

SELECT rec.record_id,
    rec.record_title,
    usr.user_id,
    usr.username,
    (
            SELECT MAX(count) FROM (
                SELECT COUNT(DISTINCT rec.record_id) AS count
                FROM records rec
                WHERE rec.record_title LIKE '%random%'
                GROUP BY rec.record_id
            ) AS x
    ) AS total_records
FROM
(
    records rec

    INNER JOIN members usr
)
WHERE rec.record_title LIKE '%random%'
GROUP BY rec.record_id
LIMIT 0, 25

Upvotes: 0

Related Questions