Using SQL computations in WHERE clause

When writing SQL statements, sometimes I want to filter the result set based on a value that must be computed out of the stored data. Here’s an example:

SELECT widgetID, count( widgetID ) AS totalWidgets
FROM table_widget_sales
WHERE totalWidgets > 1
GROUP BY widgetID
ORDER BY totalWidgets DESC

I want to see which widgets have been sold more than once, and how many times each of those remaining widgets have sold. Obviously we could pass a simpler query into a scripting language to calculate it for us, but that’s more cycles and memory. I love to squeeze as much out of my SQL statements as possible.

Problem is, when I run that query above, I get this error:

#1054 - Unknown column 'totalWidgets' in 'where clause'

My first reaction is, “I can use that column in the order by clause, why not where clauses!?” Simple, the value doesn’t exist when the WHERE clause is evaluated. The number is calculated as each matching record is passed. By the time we reach the ORDER BY clause, we have the data we need. But the WHERE clause is used to actually figure out what records we need to perform that calculation, so we’ve got a chicken-egg issue.

The solution is to use a subquery, sometimes called a “sub-select statement.” Using two select statements, we can split the duties for filtering (more than 1 widget sale) and counting (total number of sales per widget).

Here’s the working query:

SELECT w.widgetID, COUNT( ws.widgetID ) AS totalWidgets
FROM table_widgets AS w
INNER JOIN table_widget_sales AS ws ON ( w.widgetID = ws.widgetID )
WHERE (
SELECT COUNT( ws.widgetID ) AS number
FROM table_widget_sales AS ws
WHERE ws.widgetID = w.widgetID
) > 1
GROUP BY widgetID
ORDER BY totalWidgets DESC

See what’s going on? We’re doing the same count twice, but the subquery returns everything (number of sales per widget for all widgets), while the first select only grabs the results we want (sales greater than 1).