Postingan lainnya
Rating count 1,2,3,4,5 di Mysql
gais
ane punya table seperti berikut
CREATE TABLE `restaurant` (
`id_restaurant` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id_restaurant`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
insert into `restaurant`(`id_restaurant`,`name`) values (1,'Mc Donald');
insert into `restaurant`(`id_restaurant`,`name`) values (2,'KFC');
CREATE TABLE `user` (
`id_user` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
insert into `user`(`id_user`,`userName`) values (1,'Audey');
CREATE TABLE `factors` (
`factor_id` int(11) NOT NULL AUTO_INCREMENT,
`factor_clean` int(11) NOT NULL DEFAULT '0',
`factor_delicious` int(11) NOT NULL DEFAULT '0',
`id_restaurant` int(11) DEFAULT NULL,
`id_user` int(11) DEFAULT NULL,
PRIMARY KEY (`factor_id`),
KEY `id_restaurant` (`id_restaurant`),
KEY `id_user` (`id_user`),
CONSTRAINT `factors_ibfk_1` FOREIGN KEY (`id_restaurant`) REFERENCES `restaurant` (`id_restaurant`),
CONSTRAINT `factors_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
insert into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (1,1,5,1,1);
insert into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (2,0,5,1,1);
insert into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (3,1,5,1,1);
insert into `factors`(`factor_id`,`factor_clean`,`factor_delicious`,`id_restaurant`,`id_user`) values (4,3,3,1,1);
apakah bisa ya di count berdasarkan rating 1, rating 2, rating 3, rating 4, rating 5?
kalau misalnya manual akan seperti ini
SELECT `factor_clean` AS rating ,COUNT(factor_clean) FROM `factors` WHERE factor_clean = 1 GROUP BY id_restaurant
terus
SELECT `factor_delicious` AS rating ,COUNT(`factor_delicious`) FROM `factors` WHERE `factor_delicious` = 1 GROUP BY id_restaurant
hasilnya akan jadi 1 factor clean dan factor delicious, nah baru ditambah sesuai rating wherenya.
nah pertanyaan saya,
gimana caranya ya gan supaya hasilnya itu seperti ini, jadi misalnya di setiap rating 1 dijumlahin factor_clean dan factor_dilicious yang ada rating 1 nya
Terima kasih gan, soalnya ane mau konvert ke laravel bingung querynya kalau ada 10 kemungkinan. 10 kemungkinan disini maksudnya rating 1 untuk factor_clean, rating 1 untuk factor_delicious, dan seterusnya
4 Jawaban:
maaf, pertanyaannya di perbaiki gan. hasil sama dengan yang diinginkan, gambar nya sama. jadi membingungkan, hasil yang di inginkan seperti apa.. dan untuk kolom rating, itu ngambil data dari mana? penginputan manual? untuk kolom sendiri, count itu memberikan nilai rata rata atau gmna ya?
Jawaban Terpilih
dicoba, siapa tau sesuai <pre> SELECT id_restaurant, factor_clean AS rating, COUNT(factor_clean+factor_delicious) AS totalrating FROM factors GROUP BY id_restaurant, factor_clean </pre>
Terima kasih gan udah mau membantu oh ya gan
<pre> SELECT id_restaurant, factor_delicious AS rating, COUNT(factor_delicious) AS totalrating FROM factors GROUP BY id_restaurant, factor_delicious </pre>
jadi countnya itu gk berfungsi jadinya gan, jadi gk nambah sama factor_deliciousnya kalau dia sama ratingnya misalnya factor_delicious punya rating 1 dan factor_clean punya rating 1, nah jumlah countnya seharusnya 2 gan