> >>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]
