Get the row count of a select... group by mysql statement

Question

I have a table of products which contains some 2000 clothing products, each product has a grpIdent field and a productGroup field.

when I run the following query:

select count(1) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

I get a resultset of 99 rows, according to SQL Yog, containing different values pertaining to the number of rows for each group. BUT I want to return the number 99 giving the number of rows returned.

Does anybody have an idea on how I can achieve this please?

Answer 1

If you want a single value, you have to select into a variable.
Like this:

DECLARE @Count INT;
select @Count = count(1) from tblclothingitems ci where productGroup='hel' group by productGroup, grpIdent
RETURN @Count

EDIT:
Seems i got your question wrong. If i understand it right now, you could use: SELECT COUNT(DISTINCT productgroup) FROM tblclothingitems

Answer 2

SELECT COUNT(*) FROM ([Your Query])

Will return the number of rows from your query returns.

Answer 3

Use the built in function of MySQL "SQL_CALC_FOUND_ROWS" which works as follows, using your example query above;

select SQL_CALC_FOUND_ROWS count(1) 
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

Then issue a second MySQL command;

SELECT FOUND_ROWS();

And use that result within your software - you can use this in many areas, for example updating data, or where you are using a "LIMIT" clause to restrict the number of rows returned to your app.

The SQL_CALC_FOUND_ROWS tells MySQL to calculate the number of rows ignoring the "LIMIT" clause, which allows you to do something like.

SELECT SQL_CALC_FOUND_ROWS * 
FROM `myTable` 
WHERE `title` LIKE "%blah%" 
LIMIT 100,10;

To extract 10 rows starting from the 100th row, and then;

SELECT FOUND_ROWS();

Which will tell you how many rows there are with "blah" in the title field in the whole table, you can then present this info as "Showing 10 rows from 12345 total"