Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
Works great! thanks!!! –  Cristian Jan 16 at 18:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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