DuBois P. MySQL Cookbook / Recipe 3.6. WHERE Clauses and Column Aliases

Computers & Technology
Education & Reference
Business & Investing
Science & Math

MySQL Cookbook
Authors: DuBois P.
Published year: 2004
Pages: 55-57/375

Buy this book on amazon.com >>

<< Previous page
Table of contents
Next page >>

Recipe 3.6. WHERE Clauses and Column Aliases

Problem

You want to refer to a column alias in a WHERE clause.

Solution

Sorry, you cannot. But there is a workaround.

Discussion

You cannot refer to column aliases in a WHERE clause. Thus, the following statement is illegal:

mysql>

SELECT t, srcuser, dstuser, size/1024 AS kilobytes

->

FROM mail WHERE kilobytes > 500;

ERROR 1054 (42S22): Unknown column 'kilobytes' in 'where clause'

The error occurs because an alias names an output column, whereas a WHERE clause operates on input columns to determine which rows to select for output. To make the statement legal, replace the alias in the WHERE clause with the column or expression that the alias represents:

mysql>

SELECT t, srcuser, dstuser, size/1024 AS kilobytes

->

FROM mail WHERE size/1024 > 500;

+---------------------+---------+---------+-----------+
 t                    srcuser  dstuser  kilobytes 
+---------------------+---------+---------+-----------+
 2006-05-14 17:03:01  tricia   phil     2338.3613 
 2006-05-15 10:25:52  gene     tricia   975.1289  
+---------------------+---------+---------+-----------+

Recipe 3.7. Debugging Comparison Expressions

Problem

You're curious about how a comparison in a WHERE clause works. Or perhaps about why it doesn't seem to be working.

Solution

Display the result of the comparison to get more information about it. This is a useful diagnostic or debugging technique.

Discussion

Normally, you put comparison operations in the WHERE clause of a query and use them to determine which rows to display:

mysql>

SELECT * FROM mail WHERE srcuser < 'c' AND size > 5000;

+---------------------+---------+---------+---------+---------+-------+
 t                    srcuser  srchost  dstuser  dsthost  size  
+---------------------+---------+---------+---------+---------+-------+
 2006-05-11 10:15:08  barb     saturn   tricia   mars     58274 
 2006-05-14 14:42:21  barb     venus    barb     venus    98151 
+---------------------+---------+---------+---------+---------+-------+

But sometimes it's desirable to see the result of the comparison itself (for example, if you're not sure that the comparison is working the way you expect it to). To do this, just remove the WHERE clause, and put the comparison expression in the output column list, perhaps also including the values that you're comparing:

mysql>

SELECT srcuser, srcuser < 'c', size, size > 5000 FROM mail;

+---------+---------------+---------+-------------+
 srcuser  srcuser < 'c'  size     size > 5000 
+---------+---------------+---------+-------------+
 barb                 1    58274            1 
 tricia               0   194925            1 
 phil                 0     1048            0 
 barb                 1      271            0 
...

In these results, 1 means true and 0 means false.

Recipe 3.8. Removing Duplicate Rows

Problem

Output from a query contains duplicate rows. You want to eliminate them.

Solution

Use DISTINCT .

Discussion

Some queries produce results containing duplicate rows. For example, to see who sent mail, you could query the mail table like this:

mysql>

SELECT srcuser FROM mail;

+---------+
 srcuser 
+---------+
 barb    
 tricia  
 phil    
 barb    
 gene    
 phil    
 barb    
 tricia  
 gene    
 phil    
 gene    
 gene    
 gene    
 phil    
 phil    
 gene    
+---------+

That result is heavily redundant. Adding DISTINCT to the query removes the duplicate rows, producing a set of unique values:

mysql>

SELECT DISTINCT srcuser FROM mail;

+---------+
 srcuser 
+---------+
 barb    
 tricia  
 phil    
 gene    
+---------+

DISTINCT works with multiple-column output, too. The following query shows which dates are represented in the mail table:

mysql>

SELECT DISTINCT YEAR(t), MONTH(t), DAYOFMONTH(t) FROM mail;

+---------+----------+---------------+
 YEAR(t)  MONTH(t)  DAYOFMONTH(t) 
+---------+----------+---------------+
    2006         5             11 
    2006         5             12 
    2006         5             13 
    2006         5             14 
    2006         5             15 
    2006         5             16 
    2006         5             17 
    2006         5             19 
+---------+----------+---------------+

To count the number of unique values in a column, use COUNT(DISTINCT) :

mysql>

SELECT COUNT(DISTINCT srcuser) FROM mail;

+-------------------------+
 COUNT(DISTINCT srcuser) 
+-------------------------+
                       4 
+-------------------------+