如何计算具有不同值的一列中的行

I have a promblem to count rows from one column with different, here is my db and query

我有一个promblem来计算来自一列不同的行，这是我的数据库和查询

$host = 'localhost';
$db   = 'cbt';
$user = 'root';
$pass = '';

$dsn = "mysql:host=$host;dbname=$db";
$pdo = new PDO($dsn, $user, $pass);

$matematika = 'MTK';
$soal_mtk = $pdo->prepare("select count(*) from cbt.data_ujian where data_ujian.id_mapel = :mtk");
$soal_mtk->bindParam(':mtk', $matematika, PDO::PARAM_STR, 12);
$soal_mtk->execute();
$mtk = $soal_mtk->fetchColumn();

$fisika = 'FIS';
$soal_fis = $pdo->prepare("select count(*) from cbt.data_ujian where data_ujian.id_mapel = :fis");
$soal_fis->bindParam(':fis', $fisika, PDO::PARAM_STR, 12);
$soal_fis->execute();
$fis = $soal_fis->fetchColumn();

$kimia = 'KIM';
$soal_kim = $pdo->prepare("select count(*) from cbt.data_ujian where data_ujian.id_mapel = :kim");
$soal_kim->bindParam(':kim', $kimia, PDO::PARAM_STR, 12);
$soal_kim->execute();
$kim = $soal_kim->fetchColumn();

can i count all of that with one query?? please tell me if it need more information. Thankyou so much before.

我可以用一个查询计算所有这些吗？请告诉我是否需要更多信息。非常感谢你。

3 个解决方案

#1

Yes you can, You need to use GROUP BY with WHERE condition. Like this,

是的，你可以，你需要使用GROUP BY和WHERE条件。喜欢这个，

SELECT count(*),
       data_ujian.id_mapel
FROM cbt.data_ujian
WHERE (data_ujian.id_mapel = :mtk
       OR data_ujian.id_mapel = :fis
       OR data_ujian.id_mapel = :kim)
GROUP BY data_ujian.id_mapel

Above query will group the results by id_mapel column and give count from each of the category.

上面的查询将按id_mapel列对结果进行分组，并从每个类别中给出计数。

#2

If I understood you right the GROUP BY clause should do what you ask for:

如果我理解你，GROUP BY子句应该按照你的要求行事：

SELECT count(*), id_mapel FROM cbt.data_ujian GROUP BY id_mapel

#3

Try the following query -

尝试以下查询 -

SELECT Sum(data_ujian.id_mapel = 'mtk') AS 'mtk', 
       Sum(data_ujian.id_mapel = 'fis') AS 'fis', 
       Sum(data_ujian.id_mapel = 'kim') AS 'kim', 
FROM   cbt.data_ujian;

#1