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.