Get number of rows returned in MySQL query

Question

I'm having some trouble with a MySQL query I'm writing. I'd like to get a count of the number of rows that my query is returning without actually returning the rows and then using mysql_num_rows or the like.

My query is as follows:

SELECT COUNT(l.product_number_language) as counts, l.id, l.product_number, l.language,    l.product_number_language
FROM bs_products_languages l
LEFT JOIN bs_products p ON (l.product_number_language = p.product_number)
WHERE l.product_number = 'C4164' 
AND l.active='Y'
AND p.active='Y'
GROUP BY l.language

The following is what is being returned: Screenshot

And what I really want is simply a count of these rows, so in this case 3.

You're not using GROUP BY correctly. If you want the COUNT of the result set, use *_num_rows (not mysql_ functions). — Kermit
– Kermit, Commented Feb 21, 2013 at 15:26
Just to be clear, you would like to return these rows and include the total number of rows as well? You don't want to return only the total number of rows, correct? — Tom
– Tom, Commented Feb 21, 2013 at 16:07
The accepted solution doesn't work always, what if I want to return the original query in a stored procedure and also want to log the number of rows returned each time this query is executed? I don't know how it is hard to have a simple system variable to return number of rows returned by the query. — MSBI-Geek
– MSBI-Geek, Commented Aug 13, 2021 at 6:42
New comment after 8yrs and 6mos, cool! The OP did say that the total number of rows based on the query is the only need, so that's the answer the OP got. If you want an answer that doesn't change the query, in a stored procedure and get something out to a variable similar to MSSQL but in MySql, feel free to ask a new question. — Andy Refuerzo
– Andy Refuerzo, Commented Aug 20, 2021 at 7:31

Justin Johnson · Accepted Answer · 2015-09-22 17:42:14Z

41

Select count(*)
From
(
    SELECT COUNT(l.product_number_language) as counts, l.id, l.product_number, 
        l.language, l.product_number_language
    FROM bs_products_languages l
    LEFT JOIN bs_products p ON (l.product_number_language = p.product_number)
    WHERE l.product_number = 'C4164' 
    AND l.active='Y'
    AND p.active='Y'
    GROUP BY l.language
) as t

edited Sep 22, 2015 at 17:42

Justin Johnson

31.3k7 gold badges67 silver badges89 bronze badges

answered Feb 21, 2013 at 17:24

Andy Refuerzo

3,3321 gold badge35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Hennion Over a year ago

That works perfectly, can't believe I didn't think of that. Thank you, you've saved me a number of hours headache :)

lmiguelvargasf Over a year ago

@AndyRefuerzo, nice answer, it worked while other answers never worked.

MSBI-Geek Over a year ago

I don't think it is a brilliant solution, because I need to change the original query and do something extra to get a simple count of rows returned by my query. It is dead simple in SQL Server.. just use @@ROWCOUNT. You see the no of rows returned/affected in the output window when you execute any query in MySQL workbench, why can't we simply get that number via a system variable?

Collectives™ on Stack Overflow

Get number of rows returned in MySQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related