mysqlsqlcountaverage
MySQL - Calculate percentage and count number of related records
I would like to complete this code to do the following:
Calculate the number of votes and percentages of specific records, is a site of movies, where users can view and vote movies, then the website should show all films more to lower the number of votes by users. Here's the details of what I'm trying to do.
1) Calculate the number of votes in general each film.
2) Get the number of votes by separate "They are altogether 3 table[votes]" for each movie, such as:
Movie 1 There have been a total of 100 votes, 20% of users seemed Regular, 30% of users seemed Good and 50% of users seemed Very good.
3) Get the percentage of the maximum number of votes between "3 of table [votes]", if users voted more for the film was very good, I have to calculate the percentage of that, so we can create a small donut or graphic, showing movies and casts more than you liked to users, for example:
The film was voted Movie 1, with a total of 450 votes, which accounts for 50% of all votes received by user rating.
So far I have done this:
SELECT
M.name AS title_movie,
M.year AS premiere,
G.name AS gender,
COUNT(MHR.vote_id) AS total_voting_movie
FROM movies M
LEFT JOIN gender G ON M.gender_id = G.id
LEFT JOIN movies_has_rating MHR ON M.id = MHR.movie_id
GROUP BY M.id
ORDER BY M.id DESC
Graphic tables database:
The complete code for the entire database:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for gender
-- ----------------------------
DROP TABLE IF EXISTS `gender`;
CREATE TABLE `gender` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of gender
-- ----------------------------
INSERT INTO `gender` VALUES ('1', 'Action');
INSERT INTO `gender` VALUES ('2', 'Musical');
INSERT INTO `gender` VALUES ('3', 'Sci Fi');
-- ----------------------------
-- Table structure for movies
-- ----------------------------
DROP TABLE IF EXISTS `movies`;
CREATE TABLE `movies` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`year` varchar(255) DEFAULT NULL,
`gender_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of movies
-- ----------------------------
INSERT INTO `movies` VALUES ('1', 'Alien', '1979', '1');
INSERT INTO `movies` VALUES ('2', 'Aliens', '1986', '1');
INSERT INTO `movies` VALUES ('3', 'Moulin Rouge', '2001', '3');
INSERT INTO `movies` VALUES ('4', 'Guys and Dolls', '1955', '3');
INSERT INTO `movies` VALUES ('5', 'Mama Mia', '1970', '3');
INSERT INTO `movies` VALUES ('6', 'Starman', '1984', '1');
INSERT INTO `movies` VALUES ('7', 'Tron', '1982', '1');
INSERT INTO `movies` VALUES ('8', 'The Matrix', '1988', '2');
-- ----------------------------
-- Table structure for movies_has_rating
-- ----------------------------
DROP TABLE IF EXISTS `movies_has_rating`;
CREATE TABLE `movies_has_rating` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL,
`vote_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of movies_has_rating
-- ----------------------------
INSERT INTO `movies_has_rating` VALUES ('1', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('2', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('3', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('4', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('5', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('6', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('7', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('8', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('9', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('10', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('11', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('12', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('13', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('14', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('15', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('16', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('17', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('18', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('19', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('20', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('21', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('22', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('23', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('24', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('25', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('26', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('27', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('28', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('29', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('30', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('31', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('32', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('33', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('34', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('35', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('36', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('37', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('38', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('39', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('40', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('41', '1', '2');
INSERT INTO `movies_has_rating` VALUES ('42', '1', '3');
INSERT INTO `movies_has_rating` VALUES ('43', '1', '1');
INSERT INTO `movies_has_rating` VALUES ('44', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('45', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('46', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('47', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('48', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('49', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('50', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('51', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('52', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('53', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('54', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('55', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('56', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('57', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('58', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('59', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('60', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('61', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('62', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('63', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('64', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('65', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('66', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('67', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('68', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('69', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('70', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('71', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('72', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('73', '2', '3');
INSERT INTO `movies_has_rating` VALUES ('74', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('75', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('76', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('77', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('78', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('79', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('80', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('81', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('82', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('83', '2', '2');
INSERT INTO `movies_has_rating` VALUES ('84', '2', '1');
INSERT INTO `movies_has_rating` VALUES ('85', '3', '1');
INSERT INTO `movies_has_rating` VALUES ('86', '3', '1');
INSERT INTO `movies_has_rating` VALUES ('87', '3', '1');
INSERT INTO `movies_has_rating` VALUES ('88', '3', '1');
INSERT INTO `movies_has_rating` VALUES ('89', '3', '2');
INSERT INTO `movies_has_rating` VALUES ('90', '3', '3');
INSERT INTO `movies_has_rating` VALUES ('91', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('92', '4', '1');
INSERT INTO `movies_has_rating` VALUES ('93', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('94', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('95', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('96', '4', '3');
INSERT INTO `movies_has_rating` VALUES ('97', '4', '3');
INSERT INTO `movies_has_rating` VALUES ('98', '4', '1');
INSERT INTO `movies_has_rating` VALUES ('99', '4', '1');
INSERT INTO `movies_has_rating` VALUES ('100', '4', '1');
INSERT INTO `movies_has_rating` VALUES ('101', '4', '1');
INSERT INTO `movies_has_rating` VALUES ('102', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('103', '4', '3');
INSERT INTO `movies_has_rating` VALUES ('104', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('105', '4', '3');
INSERT INTO `movies_has_rating` VALUES ('106', '4', '3');
INSERT INTO `movies_has_rating` VALUES ('107', '4', '3');
INSERT INTO `movies_has_rating` VALUES ('108', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('109', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('110', '4', '2');
INSERT INTO `movies_has_rating` VALUES ('111', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('112', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('113', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('114', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('115', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('116', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('117', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('118', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('119', '5', '2');
INSERT INTO `movies_has_rating` VALUES ('120', '5', '3');
INSERT INTO `movies_has_rating` VALUES ('121', '5', '3');
INSERT INTO `movies_has_rating` VALUES ('122', '5', '3');
INSERT INTO `movies_has_rating` VALUES ('123', '5', '3');
INSERT INTO `movies_has_rating` VALUES ('124', '6', '3');
INSERT INTO `movies_has_rating` VALUES ('125', '6', '3');
INSERT INTO `movies_has_rating` VALUES ('130', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('131', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('132', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('133', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('134', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('135', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('136', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('137', '8', '1');
INSERT INTO `movies_has_rating` VALUES ('138', '8', '2');
INSERT INTO `movies_has_rating` VALUES ('139', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('140', '8', '2');
INSERT INTO `movies_has_rating` VALUES ('141', '8', '2');
INSERT INTO `movies_has_rating` VALUES ('142', '8', '2');
INSERT INTO `movies_has_rating` VALUES ('143', '8', '2');
INSERT INTO `movies_has_rating` VALUES ('144', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('145', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('146', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('147', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('148', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('149', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('150', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('151', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('152', '8', '3');
INSERT INTO `movies_has_rating` VALUES ('153', '8', '3');
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'Name user 1', '1@1.com');
INSERT INTO `users` VALUES ('2', 'Name user 2', '2@2.com');
INSERT INTO `users` VALUES ('3', 'Name user 3', '3@3.com');
-- ----------------------------
-- Table structure for votes
-- ----------------------------
DROP TABLE IF EXISTS `votes`;
CREATE TABLE `votes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of votes
-- ----------------------------
INSERT INTO `votes` VALUES ('1', 'Very good');
INSERT INTO `votes` VALUES ('2', 'Good');
INSERT INTO `votes` VALUES ('3', 'Regular');
What can I do next?
Solution
Your third part isn't clear. But the second part can be solved by:
SELECT
M.name AS title_movie,
M.year AS premiere,
G.name AS gender,
COUNT(MHR.vote_id) AS total_voting,
-- 2)
COUNT(MHR.vote_id = 1 OR NULL) AS count_very_good,
COUNT(MHR.vote_id = 2 OR NULL) AS count_good,
COUNT(MHR.vote_id = 3 OR NULL) AS count_regular
FROM movies M
LEFT JOIN gender G ON M.gender_id = G.id
LEFT JOIN movies_has_rating MHR ON M.id = MHR.movie_id
GROUP BY M.id
ORDER BY M.id DESC
http://sqlfiddle.com/#!9/5ba403/5
I'm sure you know how to calculate the percentages from that result.
Update: So OP asks to determine the vote type with the highest count and calculate its percantage within SQL. You can (but you should not) do it with the folowing query:
SELECT t.*,
CASE
WHEN highest_votes_id = 1
THEN 100 * count_very_good / total_voting
WHEN highest_votes_id = 2
THEN 100 * count_good / total_voting
WHEN highest_votes_id = 3
THEN 100 * count_regular / total_voting
END AS highest_votes_percentage
FROM (
SELECT t.*,
CASE
WHEN count_regular > count_good AND count_regular > count_very_good
THEN 3
WHEN count_good > count_very_good
THEN 2
ELSE 1
END AS highest_votes_id
FROM (
SELECT
M.id,
M.name AS title_movie,
M.year AS premiere,
G.name AS gender,
COUNT(MHR.vote_id) AS total_voting,
COUNT(MHR.vote_id = 1 OR NULL) AS count_very_good,
COUNT(MHR.vote_id = 2 OR NULL) AS count_good,
COUNT(MHR.vote_id = 3 OR NULL) AS count_regular
FROM movies M
LEFT JOIN gender G ON M.gender_id = G.id
LEFT JOIN movies_has_rating MHR ON M.id = MHR.movie_id
GROUP BY M.id
) t
) t
ORDER BY t.id DESC
http://sqlfiddle.com/#!9/5ba403/7
How ever you solve that problem in SQL, you will end up with unreasonable ugly code. You better do it in PHP.
if ($row['count_regular'] > $row['count_good'] &&
$row['count_regular'] > $row['count_very_good']
) {
$highestVoteType = 'Regular';
$highestVoteCount = $row['count_regular'];
} elseif ($row['count_good'] > $row['count_very_good']) {
$highestVoteType = 'Good';
$highestVoteCount = $row['count_good'];
} else {
$highestVoteType = 'Very Good';
$highestVoteCount = $row['count_very_good'];
}
if ($row['total_voting'] != 0) {
$highestVotePercantage = 100 * $highestVoteCount / $row['total_voting'];
} else {
$highestVotePercantage = null;
}
- Combine rows if the dates exist in another row
- Distinguish between two maximum values in a column with Power Query
- Power Query Conditional JOIN - JOIN with WHERE clause
- Calculate combinations for a range of text values in excel
- How to update a column's values in Power BI M Language
- What's the difference between DAX and Power Query (or M)?
- Sorting issue when consolidating monthly data into a year to date file. Using query, sorting by day, time, and letter
- Power Query `List.Generate` runs too slow
- Custom Colum based on specific dates in powerquery
- How do I properly use table.group in a PowerQuery query to dynamically summarize different rows and columns?
- Excel Power Query syntax issue transform multiple columns of records
- How to capitalize only first letter of a sentence
- Power Query Import For Power BI with group by on SelectRows
- Get data from config table cell when loading data source with power query
- Translate Excel Formula into Power Query
- Counting matching pairs of letters in lists of bigrams in Power Query M
- Bigrams of a string in Power Query M
- Power Query code to refer to another query (and how buffering works)
- M code for the equivalent of of Left (string, len(string)-11)
- How to add a missing column to the Transform Sample File
- How to add or insert ' (single quotes) for every string in a list in which strings are separated by commas in Power Query
- Power BI M code Split column from Lowercase to Uppercase won't include diacritics (accents)
- M Code(Power Query) to Remove Empty columns that runs fast
- How to find the value with date closest to another date
- Error when build AOSP 14 - internal error: undefined variable CTS_TEST_SUITES_DEFAULT
- Call a table using a dynamic identifier
- Problem getting the value from a cell using M in excel
- How to rename columns based on the data within them in Power Query M?
- Power Query return a field name based on parameter
- Power Query: after combining 2 tables got something strange