我有這張桌子：

tblA

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

如您所見，值可以是 1 或 0

我需要執行一個查詢，顯示每個 nameX：nameX、count_1’s、total_rows、

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

我知道我可以使用 GROUP by 和 COUNT。但是看不到如何將“total_rows”放在同一個查詢中。

這會給我名字和 1 的數量。

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

如何添加我需要的最後一列？

謝謝

對於名為 的表cristian，這是所需的查詢：

select name,SUM(value) "1s" ,SUM(1) "total"
FROM cristian GROUP BY name;

這是您的範例數據：

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)

這是查詢的輸出

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>

這些也可以

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;

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/56923