I wanted to group the number of shop but i am not sure what is the syntax to create a group that is not exist in the table. I wanted the output to be like this

Group | Number of items
  1   |    XXX
  2   |    XXX

Group 1 would have number of items that is less than 10 while group 2 would have item that is more than 10.I have the data for the number of items, but I need to create the group number and I am not sure how. Thank you in advance.

Way I have tried:

SELECT 
case when b.item_stock < 10 then count(a.shopid) else null end as Group_1,
case when b.item_stock >= 10 or b.item_stock < 100 then count(a.shopid) else null end as Group_2


FROM `table_a` a
left join `table_b` b
on a.id= b.id

where registration_time between "2017-01-01" and "2017-05-31"

group by b.item_stock

LIMIT 1000

CodePudding user response：

From the example you've shared you were close to solving this one, just need to tweak your case statement.

The case statement in your query is splitting the groups into two separate columns, whereas you need these groups in one column with the totals to the right.

Consider the below change to your select statement.

case when b.item_stock < 10 then "Group_1"
when b.item_stock >= 10 then "Group_2" else null end as Groups,
count(a.shop_id) as total

Schema (MySQL v5.7)

CREATE TABLE id (
  `shop_id` VARCHAR(5),
  `item_stock` INTEGER
);

INSERT INTO id
  (`shop_id`, `item_stock`)
VALUES
  ('ID001', '40'),
  ('ID002', '20'),
  ('ID003', '30'),
  ('ID004', '9'),
  ('ID005', '44'),
  ('ID006', '22'),
  ('ID007', '28'),
  ('ID008', '35'),
  ('ID009', '20'),
  ('ID010', '4'),
  ('ID011', '5'),
  ('ID012', '45'),
  ('ID013', '29'),
  ('ID014', '8'),
  ('ID015', '40'),
  ('ID016', '26'),
  ('ID017', '31'),
  ('ID018', '48'),
  ('ID019', '45'),
  ('ID020', '13');

Query #1

SELECT 
case when item_stock < 10 then "Group_1"
when item_stock >= 10 then "Group_2" else null end as Groups,
count(shop_id) as total
FROM id group by 1;

Benefit of this solution is that it is easily extended to any number of ranges just by adding those into range_bucket function -
for example : range_bucket(item_stock, [0, 10, 50, 100, 1000])