> >>select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2,>>SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where >>type_id=5879 group by object_id
and it complains about unknown column C. Anyone have any insite on how to do this?
Syntax, my dear boy. It's just a hunch, but shouldn't you declare 'count(*) AS
C' before you start trying to do calculations on it? Try this:
Won't work. You can't refer to a column alias elsewhere in the column output list.
You might run one query to get the counts into a temporary table, then another to join the temp table to the original one.
select count(*) as C, SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3 from object_hist where type_id=5879 group by object_id
Edward Dudlik Becoming Digital www.becomingdigital.com
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
