MySQL Forums
Forum List  »  Newbie

Building rankings
Posted by: Andrea Nuzzi
Date: December 13, 2014 06:00PM

Version 5.5.0-m2-community


CREATE TABLE `Ranking` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(9) DEFAULT NULL,
`Type` varchar(1) DEFAULT NULL,
`Code` varchar(2) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


INSERT INTO ranking
(`id`, `Name`, `Type`, `Code`)
VALUES
(1, 'House', 'A', 'F1'),
(2, 'House', 'A', 'F2'),
(3, 'House/1', 'A', 'F3'),
(4, 'f/House/j', 'A', 'F4'),
(5, 'House', 'B', 'F5'),
(6, 'House', 'A', 'F1'),
(7, 'House/p', 'A', 'F1'),
(8, 'Boat', 'A', 'F1'),
(9, 'Car', 'A', 'F2'),
(10, 'Car/1', 'A', 'F3'),
(11, 'f/Boat', 'A', 'F4'),
(12, 'Car', 'B', 'F5'),
(13, 'Pen', 'A', 'F1'),
(14, 'Boat/p', 'A', 'F1')
;


Consider this table as a small representation of the reality, where names are much more than House, Boat, Car, Pen ... and it is not predictable how they would look like in a table.

The ranking I need is: for a given Type, I need to group by Name (in like statement) then count distinct codes.

In the end, the results look like the following sets:


For Type = A Name #

House 4
Boat 2
Car 2
Pen 1



For Type = B Name #

Car 1
House 1


Following is a snippet of code that works just for 1 Name (hard coded) but now I need to get a general query for any name:

SELECT 
    type AS "Type", 
    CASE 
       WHEN name LIKE '%House%' THEN 'House'  
    END AS "Name",
    COUNT(DISTINCT code) AS "Count Code" 
FROM ranking
GROUP BY 
    type, 
    CASE 
       WHEN name LIKE '%House%' THEN 'House'  
    END 
ORDER BY type

Other suggestions are super welcome. Thank you for helping me.

Options: ReplyQuote


Subject
Written By
Posted
Building rankings
December 13, 2014 06:00PM
December 13, 2014 06:26PM
December 13, 2014 10:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.