How to group items by rows

Question

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

user_5221944 · Answer

Below is the BigQuery way of doing this

select 'group_' || range_bucket(item_stock, [0, 10]) as group_id,
  count(*) as number_of_items
from your_table 
group by group_id

if apply to dummy data like

with your_table as (
  select 'ID001' shop_id, 40 item_stock union all
  select 'ID002', 20 union all
  select 'ID003', 30 union all
  select 'ID004', 9 union all
  select 'ID005', 44 union all
  select 'ID006', 22 union all
  select 'ID007', 28 union all
  select 'ID008', 35 union all
  select 'ID009', 20 union all
  select 'ID010', 4 union all
  select 'ID011', 5 union all
  select 'ID012', 45 union all
  select 'ID013', 29 union all
  select 'ID014', 8 union all
  select 'ID015', 40 union all
  select 'ID016', 26 union all
  select 'ID017', 31 union all
  select 'ID018', 48 union all
  select 'ID019', 45 union all
  select 'ID020', 13 
)

output is

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])

user_9784909 · Answer

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;

Groups	total
Group_1	4
Group_2	16

View on DB Fiddle

Tom

How to group items by rows

2 Answers