For a table called cristian
, here is the needed query:
select name,SUM(value) "1s" ,SUM(1) "total"
FROM cristian GROUP BY name;
Here is your sample data:
mysql> use test
Database changed
mysql> drop table cristian;
Query OK, 0 rows affected (0.01 sec)
mysql> create table cristian
-> (
-> id int not null auto_increment primary key,
-> name varchar(25),value int not null
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into cristian (name,value) values
-> ('nameA',1),('nameA',0),('nameB',0),
-> ('nameC',0),('nameC',1),('nameC',1);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from cristian;
+----+-------+-------+
| id | name | value |
+----+-------+-------+
| 1 | nameA | 1 |
| 2 | nameA | 0 |
| 3 | nameB | 0 |
| 4 | nameC | 0 |
| 5 | nameC | 1 |
| 6 | nameC | 1 |
+----+-------+-------+
6 rows in set (0.00 sec)
and here is the query's output
mysql> select name,SUM(value) "1s" ,SUM(1) "total"
-> FROM cristian GROUP BY name;
+-------+------+-------+
| name | 1s | total |
+-------+------+-------+
| nameA | 1 | 2 |
| nameB | 0 | 1 |
| nameC | 2 | 3 |
+-------+------+-------+
3 rows in set (0.14 sec)
mysql>
These would also work
select name,SUM(value) "1s" ,COUNT(1) "total"
FROM cristian GROUP BY name;
select name,SUM(IF(value=1,1,0)) "1s" ,COUNT(1) "total"
FROM cristian GROUP BY name;
Give it a Try !!!