MySQL count for multiple conditions

MySQL COUNT allows you to count rows in your MySQL database, a useful way to use this is to apply a condition like:

SELECT COUNT(*) FROM invoices WHERE `paid` = 1

That returns the amount of rows that have paid as 1.

A good way of using COUNT is to apply a GROUP BY and column reference, this can give you a greater look at the database makeup.

Saying that a database which has coffee drank per day as amount, these values could be anything from 0 through to (hopefully no more than) 6. To get the count of days i had 0 coffees or 1 coffee or 2 coffee etc I would run this:

SELECT amount, COUNT(*) FROM coffee GROUP BY amount

Which outputs:


Another way which would suit a different example is seeing the count of fruit sold but only for certain fruit:

SELECT fruit, COUNT(*) FROM sales WHERE fruit IN ('LEMON', 'LIME', 'ORANGE') GROUP BY fruit

Thus returning the count only for lemons, limes and oranges.