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: [code=“sql”]SELECT widgetID, count( widgetID ) AS totalWidgets

FROM table_widget_sales

WHERE totalWidgets > 1

GROUP BY widgetID

ORDER BY totalWidgets DESC[/code] 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: [code=“sql”]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[/code] 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).