get max, min, count and mode (occurrence)-CodePudding

I have an items table in my database that i want my query to process the values and give me the data of the max price, min price, most recurrent max price in that specific item category and no of items (and ignore the ones that are null), so here is my items table:

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

I want the query to return this result:

category	min_price	max_price	price_mode	no_items
kids	10	100	100	3
adult	20	500	200	5
both	20	100	100	2
misc	null	null	null	1

so just to further explain the adult lowest price in 20 and highest is 500 and the 100 and 200 max_price has 2 occurrences both i want to take the highest as the price_mode which is 200 in this case and the no_items is just the count of how many times adult is shown in the table.

am struggling to get the mode honestly and grouping it correctly to get the output I want.

Below is the commands to create table and feed it with data. Tried to put it in SqlFiddle but that's not working for me i don't know why.

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(6) unsigned NOT NULL,
  `category` TEXT NOT NULL,
  `min_price` FLOAT DEFAULT NULL,
  `max_price` FLOAT DEFAULT NULL,
  PRIMARY KEY (`id`)
  );
INSERT INTO `items` (`id`, `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);

CodePudding user response：

Maybe this query will help

with maximumvaluecounts
as ( select 
     count(max_price) as c, category, max_price 
     from yourtable
     group by category 
),
maximumcountpercategory
as ( select category,max(c) as c 
     from maximumvaluecounts 
     group by category
),
modes as ( select category, max_price as modevalue 
           from maximumcountpercategory m1
           join maximumvaluecounts m2
              on m1.category=m2.category
               and m1.c=m2.c
          )
, others as (
select 
category,
min(min_price) as min_price,
max(max_price) as max_price,
count(max_price) as no_items
from yourtable 
group by category 
)
select o.*, m.modevalue as price_mode
from others o join 
modes m on o.category=m.category

CodePudding user response：

Your create table insert data syntax doesn't work in fiddle because your data VALUES are for just 3 columns whereby you define 4 columns in the INSERT:

INSERT INTO `items` (`id`, `category`, `min_price`, `max_price`) VALUES
                             ('kids' ,     10     ,      100),
             /*where's the value for `id`?*/
...

If you remove id from the INSERT syntax, it won't work as well because you've set it as PRIMARY KEY so it can't be empty. What you can do in addition to removing id from INSERT is to define AUTO_INCREMENT on the id column:

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
....

Now, to get the expected result on your price_mode, you may want to try using GROUP_CONCAT() with ORDER and define which of the data in there that you want to return. Let's say you do GROUP_CONCAT(max_price ORDER BY max_price DESC) to return the set with max_price in descending order like this:

SELECT category,
       MIN(min_price),
       MAX(max_price),
       GROUP_CONCAT(max_price ORDER BY max_price DESC),
       COUNT(*)
FROM items
GROUP BY category;

Then you'll get a result like this:

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

So, there's a consistent pattern in the GROUP_CONCAT() result that you probably can work out with. Assuming that you want the second largest value in the set, you can apply SUBSTRING_INDEX() twice to get it like this:

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;

This return the following result:

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

Demo fiddle