0

I have a table with the following structure:

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,3
2,East,1_1_2,1,1
3,East,1_1_3,1,0
4,East,1_1_4,1,0
5,East,1_1_5,1,2
6,East,1_2_1,1,1
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4 
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,0

I would like to aggregate by remaining where remaining is not 0.

The above example should return a table with a commas delimited shelf_id and remaining with a total of remaining that looks like:

shelf_id
1_1_1, 1_1_2
remaining
3,1
total
4

shelf_id
1_1_5, 1_2_1
remaining
2,1
total
3

shelf_id
1_2_3, 1_2_4, 1_2_5, 1_3_1, 1_3_2, 1_3_3
remaining
4,4,4,4,4,4
total
24

I am using MariaDB 10.2.31.

0
WITH 
cte1 AS
(
SELECT shelf_id, remaining, SUM(remaining = 0) OVER (ORDER BY id) group_num
FROM test
)
SELECT GROUP_CONCAT(shelf_id) shelf_id,
       GROUP_CONCAT(remaining) remaining,
       SUM(remaining) total
FROM cte1
WHERE remaining != 0
GROUP BY group_num;

fiddle

| improve this answer | |
  • Thank you again Akina! Very much appreciated :) – andyy15 May 7 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.