Use COUNT() to count things: For example, this will give you the number of names in mytable (null values won’t count):
SELECT
COUNT(name)
FROM mytable;
Use COUNT() with WHERE to count things where some condition is true:
SELECT
COUNT(name)
FROM mytable;
WHERE name LIKE 'John%';
When you need to count something per hour, per day or per some other time period use COUNT() with GROUP BY. For example, if ‘dt’ is a DATETIME column, you can make a summary table with counts by day using a script like this:
SELECT
DATE(dtime) L,
COUNT(DISTINCT ip)
FROM ACCESS
GROUP BY L
ORDER BY L;
L COUNT(DISTINCT ip)
---------- ------------------
2009-08-26 191
2009-08-27 180
2009-08-28 158
2009-08-29 131
2009-08-30 185
2009-08-31 170
2009-09-01 232
2009-09-02 187
2009-09-03 219
2009-09-04 161
To make the table a chart, add PLOT:
PLOT
ALL AXISLABELS, LIGHT RED BAR
WITH
TITLE Y "Distinct IP Addresses"
NO SIDES
PAGE HALF
HORIZONTAL GRIDLINES
SELECT
DATE(dtime) L,
COUNT(DISTINCT ip)
FROM ACCESS
GROUP BY L
ORDER BY L;
You can use a similar technique when you need to count something per range of values. Use COUNT() as before, and do a GROUP BY on labels for the ranges:
PLOT
ALL AXISLABELS, LIGHT RED BARS, DATALABELS
WITH
TITLE Y "Count"
NO SIDES
SELECT
CONCAT(CAST(statusCode / 100 AS UNSIGNED) *100,
'-',
CAST((statusCode / 100 AS UNSIGNED)+1) * 100) L, COUNT(statusCode), COUNT(statusCode)
FROM ACCESS
GROUP BY L;
Alternatively, you can use the built-in Histogram tool in the Charts menu. The tool has the advantage that it can create empty "buckets". It is also useful for making frequency distributions where each bucket is 1 unit wide.
See also: Other MySQL aggregator functions, eg. AVG(), SUM(), MAX(), and MIN() can be used with GROUP BY.