ghabriel ghabriel - 5 months ago 9
MySQL Question

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);

CREATE TABLE `data_user` (
`id` int(10) NOT NULL,
`nama` varchar(20) NOT NULL,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`level` varchar(20) NOT NULL,
`pin` int(10) NOT NULL,
`pin_ujian` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `data_user` (`id`, `nama`, `username`, `password`, `level`, `pin`, `pin_ujian`) VALUES
(1, 'admin', 'admin', '250788', 'administrator', 250809, '1234'),
(2, 'guest', 'siswa', '1234', 'guest', 1234, '1234');

ALTER TABLE `data_user`
ADD PRIMARY KEY (`id`);

ALTER TABLE `data_user`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

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

Answer

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.

Comments