By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `items` (
`id` int(6) unsigned NOT NULL AUTO_INCREMENT,
`category` TEXT NOT NULL,
`min_price` FLOAT DEFAULT NULL,
`max_price` FLOAT DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `items` (`category`, `min_price`, `max_price`) VALUES
('kids', 10, 100),
('adult', 20, 200),
('both', null, null),
('adult', 20, 100),
('adult', 50, 100),
('adult', 50, 200),
('kids', 20, 100),
('both', 20, 100),
('kids', null, null),
('adult', 10, 500),
('misc', null, null);
SELECT * FROM items;
| id | category | min_price | max_price |
|---|---|---|---|
| 1 | kids | 10 | 100 |
| 2 | adult | 20 | 200 |
| 3 | both | null | null |
| 4 | adult | 20 | 100 |
| 5 | adult | 50 | 100 |
| 6 | adult | 50 | 200 |
| 7 | kids | 20 | 100 |
| 8 | both | 20 | 100 |
| 9 | kids | null | null |
| 10 | adult | 10 | 500 |
| 11 | misc | null | null |
SELECT category,
MIN(min_price),
MAX(max_price),
GROUP_CONCAT(max_price ORDER BY max_price DESC),
COUNT(*)
FROM items
GROUP BY category
| category | MIN(min_price) | MAX(max_price) | GROUP_CONCAT(max_price ORDER BY max_price DESC) | COUNT(*) |
|---|---|---|---|---|
| adult | 10 | 500 | 500,200,200,100,100 | 5 |
| both | 20 | 100 | 100 | 2 |
| kids | 10 | 100 | 100,100 | 3 |
| misc | null | null | null | 1 |
SELECT category,
MIN(min_price) AS min_price,
MAX(max_price) AS max_price,
SUBSTRING_INDEX(
SUBSTRING_INDEX(
GROUP_CONCAT(max_price ORDER BY max_price DESC),',',2),',',-1)
AS price_mode,
COUNT(*) AS no_items
FROM items
GROUP BY category;
| category | min_price | max_price | price_mode | no_items |
|---|---|---|---|---|
| adult | 10 | 500 | 200 | 5 |
| both | 20 | 100 | 100 | 2 |
| kids | 10 | 100 | 100 | 3 |
| misc | null | null | null | 1 |