How to count total number of Groups?

[HoboTraveler]

Hi All,

I need to count the total number of groups in a MySQL database.

The following query returns the number of rows in each group.

SELECT
COUNT(entryid) AS numrows
FROM
`table`

Ideas welcome..

TIA

[oku86]

what i use to count rows in sql and display the results

$query = "SELECT row1, COUNT(row2) FROM database GROUP BY row3";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "<span class='style19'>There are<b>". $row['COUNT(row2)'] ." records</b>";
echo "<br /></span>";
}

the outcome would be - There are *Number from the count query* records

[HoboTraveler]

Yes, but I do not need to count records.

I need to count the total number of groups.

[eelixduppy]

What are your groups? What are you actually counting?

[WesleyC]

I recommend that you try oku's code--I believe it will work.

I've run similar queries before; the GROUP BY clause should be applied to the "group" column. The intent is that MySQL will group all records with a certain value in the "group" column together, and will give you a count column containing the number of entries that it grouped together.

Thus, if you have the following records...

Table: users
-------------------
id ¦ name ¦ group
-------------------
1 ¦ Test ¦ 1
2 ¦ Hello ¦ 1
3 ¦ Bob ¦ 2
4 ¦ Jim ¦ 2
5 ¦ Kate ¦ 3
6 ¦ Bill ¦ 1
7 ¦ Sue ¦ 1
8 ¦ Tim ¦ 3
9 ¦ Allen ¦ 2

And you run the query...

SELECT COUNT(id), group as users_in_group FROM users GROUP BY group

You should receive the following output:

Table: users
-----------------------
users_in_group ¦ group
-----------------------
  4  ¦ 1
  3  ¦ 2
  2  ¦ 3

Hope this helps!

[edited by: WesleyC at 3:38 pm (utc) on May 14, 2008]