I tried that way also Unfortunately I get ERROR 1054: Unknown column 'C' in 'field list'.
Btw... In case it matters ver4.0 -----Original Message----- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 5:31 PM To: Christopher Knight; MySQL List Subject: Re: sum on counts > >>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: 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 ----- Original Message ----- From: "Christopher Knight" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Thursday, 12 June, 2003 11:22 Subject: sum on counts Can you sum on counts? What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects. >>select object_id from object_hist where type_id=5879; +-----------+ | object_id | +-----------+ | 2121 | | 3234 | | 2121 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4889 | | 3091 | | 3092 | | 3092 | | 3103 | | 3103 | | 1390 | | 1874 | | 3234 | | 2121 | | 4889 | +-----------+ >>select object_id, count(*) from object_hist where type_id=5879 group >>by object_id; +-----------+----------+ | object_id | count(*) | +-----------+----------+ | 1390 | 1 | | 1874 | 1 | | 2121 | 3 | | 3091 | 1 | | 3092 | 2 | | 3103 | 2 | | 3234 | 2 | | 4876 | 7 | | 4889 | 2 | +-----------+----------+ What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects ...something like this... (in 1 row) +--------+--------+--------+-----------+-------+ | VIEW_1 | VIEW_2 | VIEW_3 | VIEW_MORE | TOTAL | +--------+--------+--------+-----------+-------+ | 3 | 4 | 1 | 1 | 9 | +--------------------------------------+-------+ I read this article http://www.devshed.com/Server_Side/MySQL/MySQLWiz/ and got all excited and tried to write something like this >>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? Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
