CODEWITHSUNDEEP
mysqlselectgroup-byrowcount
EvilKermitSaurus
EvilKermitSaurus

Reputation: 305

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

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?

Upvotes: 1

Views: 12429

Answers (5)

mckenzm
mckenzm

Reputation: 1820

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

will return counts for different values of grpIdent.

Point 1. You could nest the table and select count(*) to count the rows.

Point 2. This below is the preferred query for the cardinality of the values.

select count (distinct grpIdent) 
from tblclothingitems ci 
where productGroup='hel';

Upvotes: 1

Remawi
Remawi

Reputation: 21

select **@@Rowcount** as myResultRowsCount
from tblclothingitems ci 
where productGroup='hel' 
group by productGroup, grpIdent

Upvotes: 2

Dave Rix
Dave Rix

Reputation: 1679

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"

Upvotes: 0

Marks
Marks

Reputation: 3663

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

Upvotes: 0

Ben S
Ben S

Reputation: 69342

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

Will return the number of rows from your query returns.

Upvotes: 2

Related Questions