Counting rows in single query?

[kilnakorr]

I can't seem to figure out how to count the rows for more than one thing in a single query (I know it must be possible :))

if I have a column called 'animal' how can I count the rows for value 'cat', 'dog', 'horse'?

eg.

number of rows for cat =10

number of rows for dog =12

number of rows for horse =25

[Barand]

SELECT animal
     , COUNT(*) as total
FROM mytable
GROUP BY animal;

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

[kilnakorr]

thanks.

It works as intended, but how can I distinct between the output?

I need to create a variable for each of the outputs, so I have to be able to output the count for just 'cat' ,'dog' and so on.

 

using a while loop will just output '10 12 15'

[Barand]

10 minutes ago, kilnakorr said:

using a while loop will just output '10 12 15'

It will if you only out put the total column - but the animal type is there too...

mysql> CREATE TABLE myanimal (animal varchar(10) );
Query OK, 0 rows affected (0.55 sec)

mysql> INSERT INTO myanimal VALUES ('cat'),('dog'),('cat'),('dog'),('horse'),('horse'),('duck'),('dog'),('cat');
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from myanimal;
+--------+
| animal |
+--------+
| cat    |
| dog    |
| cat    |
| dog    |
| horse  |
| horse  |
| duck   |
| dog    |
| cat    |
+--------+
9 rows in set (0.00 sec)

mysql> select animal
    ->      , count(*) as total
    -> FROM myanimal
    -> GROUP BY animal;
+--------+-------+
| animal | total |
+--------+-------+
| cat    |     3 |
| dog    |     3 |
| duck   |     1 |
| horse  |     2 |
+--------+-------+

In PHP...

$res = $db->query("SELECT animal
                        , COUNT(*) as total
                   FROM myanimal
                   GROUP BY animal
                  ");
$totals = [];
foreach ($res as $row) {
    $totals[ $row['animal'] ]  = $row['total'];
}

// now you can access each animal's total

echo $totals['horse'];          //--> 2 
echo $totals['dog'];            //--> 3