Output Extra Rows from Query 1

[maxhugen]

I have a table `review` with column `rating` that holds a value of 1 to 5.

I need to show the number of votes for each rating. I tried:

SELECT
    rating
    , count(review_id) as rating_count
FROM
    review
GROUP BY rating
ORDER BY rating DESC;

The problem is I need to return a zero for any grouped rating with no records, eg:

rating    rating_count
  5             0
  4             8
  3             3
  2             1
  1             0

Can anyone suggest how I can make sure that I always get 5 rows returned by such a query pls?

MTIA

Max Hugen
Australia

 

[r937]

SELECT x.rating
     , COUNT(r.review_id) AS rating_count
  FROM ( SELECT 1 AS rating
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5 ) AS x
LEFT OUTER
  JOIN review AS r
    ON r.rating = x.rating
GROUP 
    BY x.rating
ORDER 
    BY x.rating DESC

r937.com | rudy.ca

 

[maxhugen]

Many thanks, that was exactly what I was after!



Max Hugen
Australia

 

[tsuji]

[0] One concern I would have is to find the admissible rating being hard scripted. There should be the place where improvement can be made.

[1] If the admissible set of rating is stored in a lookup table, the reference to it can then be made with some ease and avoided hard scripting the values.

[2] Suppose admissible set of rating is done in mysql through its proprietary ENUM() type? It is for this direction that I would like to make a slight development of the solution.

[2.1] Suppose the table is set up like this.
[tt]
CREATE TABLE IF NOT EXISTS [blue]tblname[/blue] (
review_id INT UNSIGNED UNIQUE,
rating ENUM ('1','2','3','4','5') NOT NULL,
PRIMARY KEY (review_id)
);
[/tt]
[2.2] I would use a store procedure being slight development from a use's contribution to the ENUM() documentation, adding a outparam. The result would then be insert into a prepared statement for execution.
[tt]
DELIMITER $$
DROP PROCEDURE IF EXISTS GetEnumChoiceList2$$

CREATE PROCEDURE `GetEnumChoiceList2`(IN dbName VARCHAR(80), IN tableName VARCHAR(80), IN columnName VARCHAR(80), OUT qout VARCHAR(255))
BEGIN
-- tsuji note: my slight development from ENUM() document's user contribution, adding an outparam.
-- ref

http://dev.mysql.com/doc/refman/5.0/en/enum.html

-- user's contribution of Clifford Hill on September 20 2006 7:58pm

-- this translates an enum's choices into a single-column table.
DECLARE subQuery TEXT;
DECLARE firstPos INT(11);

-- This gets a string like "enum('value1','value2','value3')"
SET subQuery = (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = dbName AND TABLE_NAME = tableName AND COLUMN_NAME = columnName));

-- trim off the leading "enum("
SET subQuery = SUBSTRING_INDEX(subQuery, "enum(", -1);
-- and the trailing ")"
SET subQuery = SUBSTRING_INDEX(subQuery, ")", 1);
-- replace all the "," with " UNION SELECT "
SET subQuery = REPLACE(subQuery,","," UNION SELECT ");
SET subQuery = INSERT(subQuery,1,0,"SELECT ");
-- find the first position of "UNION"
SET firstPos = INSTR(subQuery, 'UNION');
-- insert the column name "Options" before that first "UNION"
SET subQuery = INSERT(subQuery, firstPos, 0, "AS `Options` ");
-- modification : output parameter qout
SET qout := subQuery;
/* commented out
-- This is to execute the query. Until I figure out a better way.
SET @enumProcQuery = (subQuery);
PREPARE STMT FROM @enumProcQuery;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
*/
END$$

DELIMITER ;
[/tt]
[2.3] The query operation is then done like this.
[tt]
SET @databasename := '[blue]testmysql[/blue]';
SET @tablename := '[blue]tblname[/blue]';
SET @tmp :='';
CALL GetEnumChoiceList2(@databasename,@tablename,'rating',@tmp);

SET @q := CONCAT('SELECT S.Options as rating,COUNT(review_id) AS rating_count FROM (',
@tmp,
') AS S LEFT OUTER JOIN ',
@tablename, ' ',
'as R ON R.rating=S.Options GROUP BY S.Options ORDER BY S.Options DESC;');

PREPARE doit FROM @q;
EXECUTE doit;
DEALLOCATE PREPARE doit;
[/tt]