1

I had this table:

tblA

id | name | value
1    nameA    1
2    nameA    0
3    nameB    0
4    nameC    0
5    nameC    1
6    nameC    1

As you see, Value can be 1 or 0

I need to perform a query that shows this for every nameX: nameX, count_1's, total_rows,

name  | 1's | total
nameA    1      2
nameB    0      1
nameC    2      3

I know that i can use GROUP by and COUNT. But don't see how to put the "total_rows" in same query.

This will give me name and number of 1's.

SELECT name, value FROM tblA WHERE value = "1" GROUP BY name 

How to add the last column i need?

Thanks

0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.