CODEWITHSUNDEEP
phpmysql
ghabriel
ghabriel

Reputation: 23

How to count rows from one column with different value

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

$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.

Upvotes: 2

Views: 106

Answers (3)

kumaravel
kumaravel

Reputation: 74

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;

Upvotes: 0

Alok Patel
Alok Patel

Reputation: 8022

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

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.

Upvotes: 1

Marc Compte
Marc Compte

Reputation: 4819

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

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

Upvotes: 0

Related Questions