TW Blog :: Archives for November 2003

MySQL Summarizing

Relates to MySQL and Databases

Another little SQL snippet here for MySQL. This one is useful when summarizing <select> data from an HTML form (perhaps stored as an ENUM datatype).

SELECT @total := COUNT(*),
       @offices := COUNT(IF(type='offices',1,NULL)),
       @units := COUNT(IF(type='units',1,NULL))
       FROM `tblunits`;                   
SELECT CONCAT(@total, ' [', 
              @offices, ' office', 
              IF(@offices != 1,'s',''), ', ', 
              @units, ' unit', 
              IF(@units != 1, 's',''), 
              ']');                    

Once again these two queries utilise variables, and make use of conditional testing within a COUNT function to calculate the summaries. The CONCAT could just as easily have been done in PHP, but I chose to use the PEAR::Package::DB getOne() call. Short and simple!

Posted on Friday, Nov 14, 2003 at 03:42:03.