Hack 29. Other Ways to COUNT

When you want to count rows you can choose one column as the argument to COUNT. But you don't have to choose any column at all.

Suppose you need to know how many comics you have in your table of performers (see Table 5-7).

Table 5-7. The performers table

name status appearance medium
Abbott Foil
NULL
TV
Bing Foil Suave Cinema
Costello Comic Stout TV
Groucho Comic Mustachioed Cinema
Hardy Foil Stout Cinema
Hope Comic
NULL
Cinema
Laurel Comic Thin Cinema
Belushi Comic Stout Cinema

It makes a difference which column you count. Notice that the count skips the NULL values:

mysql> SELECT COUNT(name), COUNT(status), COUNT(appearance), COUNT(medium)
 -> FROM performer WHERE status='Comic';
+-------------+---------------+-------------------+---------------+
| COUNT(name) | COUNT(status) | COUNT(appearance) | COUNT(medium) |
+-------------+---------------+-------------------+---------------+
| 5 | 5 | 4 | 5 |
+-------------+---------------+-------------------+---------------+

You can use COUNT(1) to count all the rows:

mysql> SELECT COUNT(1) FROM performers WHERE status='Comic';
+-----------+
| COUNT(1) |
+-----------+
| 5 |
+-----------+

This neatly avoids you having to choose a particular field to count. Of course, you could use COUNT(*), which means the same thing.

5.6.1. Counting with a Condition

Instead of using COUNT, you can use SUM on a condition. In place of the SELECT/WHERE statement you can use SUM with CASE:

mysql> SELECT SUM(CASE WHEN status='Comic' THEN 1 END) FROM performer;
+------------------------------------------+
| SUM(CASE WHEN status='Comic' THEN 1 END) |
+------------------------------------------+
| 5 |
+------------------------------------------+

In MySQL, you can do the same thing with fewer words. In MySQL, the value TRUE is represented by 1 and FALSE by 0. So in MySQL, you can simply write:

mysql> SELECT SUM(status='Comic') FROM performer;
+---------------------+
| SUM(status='Comic') |
+---------------------+
| 5 |
+---------------------+

The rest of the examples in this hack use the shorter MySQL syntax; you also can run these queries using the CASE statement.

The advantage of using a SUM rather than a filter is that you can count a bunch of different conditions in one statement. For instance, you can count comics and foils in the same query:

mysql> SELECT SUM(status='Comic') AS Comics
 -> ,SUM(status='Foil') AS Foils
 -> ,SUM(1) AS Total
 -> FROM performer;
+--------+-------+-------+
| Comics | Foils | Total |
+--------+-------+-------+
| 5 | 3 | 8 |
+--------+-------+-------+

Alternatively, you can show the percentage of performers who are comics alongside the percentage of performers who are stout:

mysql> SELECT FLOOR(100*SUM(status='Comic')/SUM(1)) AS "Comics %"
 -> ,FLOOR(100*SUM(appearance='stout')/SUM(1)) AS "Stout %"
 -> FROM performer;
+----------+---------+
| Comics % | Stout % |
+----------+---------+
| 62 | 37 |
+----------+---------+

You also can break this down to see the percentage of comics who are stout against the percentage of performers who are stout:

mysql> SELECT
 -> FLOOR(100*SUM(status='Comic' AND appearance='stout')/
 -> SUM(status='Comic')) AS "Stout as % of Comic"
 -> ,FLOOR(100*SUM(appearance='stout')/SUM(1)) 
 ->  AS "Stout as % of Performer"
 -> FROM performer;
+---------------------+-------------------------+
| Stout as % of Comic | Stout as % of Performer |
+---------------------+-------------------------+
| 40 | 37 |
+---------------------+-------------------------+

You can break this down further with a GROUP BY. If you want to see how the propensity to stoutness among comics varies between TV and cinema you can use:

mysql> SELECT
 -> medium
 -> ,FLOOR(100*SUM(status='Comic' AND appearance='stout')/
 -> SUM(status='Comic'))
 -> AS "Stout as % of Comic"
 -> ,FLOOR(100*SUM(appearance='stout')/SUM(1))
 ->  AS "Stout as % of Performer"
 -> FROM performer
 -> GROUP BY medium;
+--------+---------------------+-------------------------+
| medium | Stout as % of Comic | Stout as % of Performer |
+--------+---------------------+-------------------------+
| Cinema | 25 | 33 |
| TV | 100 | 50 |
+--------+---------------------+-------------------------+

 

5.6.1.1. Access

Microsoft Access uses the value 1 for TRUE and 0 for FALSE, so with Access you must remember to negate your answer:

SELECT -SUM(status='Comic') FROM performers


SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net