I have the following structure of table:

There are many more rows in the actual table. This is just an example.

I want to make a table that keeps 'category', makes another column 'user_id_type' that labels even and odd, and another new column (value_sum) that sums all of the 'value' based on 'category' and 'user_id_type'.

So, it will have only four rows, with 'A' 'odd' and the sum, 'A' 'even' and the sum, 'B' 'odd' and the sum, 'B' 'even' and the sum.

I want it to look like this:

Schema:

CREATE TABLE table_1 (
  `category` VARCHAR(2),
  `user_id` INT(2), 
  `value` DECIMAL(3,2)
 );
INSERT INTO table_1
(`category`, `user_id`, `value`)
VALUES
('A', 1, 0.01),
('A', 2, 0.05),
('A', 3, 0.09),
('A', 4, 0.12),
('B', 1, 0.34),
('B', 2, 0.27),
('B', 3, 0.08),
('B', 4, 0.12)
;

CodePudding user response：

You can use MOD with a case expression to determine odd or even, and then use the results as a derived table to aggregate from:

select a.category
  , a.user_id_type
  , sum(a.value)
from
(
  SELECT t.category
    , case
        when MOD(t.user_id, 2) = 0 then 'Even'
        else 'Odd'
      end user_id_type
    , t.value
  FROM tbl t
) a
group by a.category, a.user_id_type
;

Edit: Adding Lemon's recommendation for compactness

Here is the example with Lemon's compacted version:

select category
  , user_id_type
  , sum(value)
from
(
  SELECT category
    , IF(MOD(t.user_id, 2)=0, 'even', 'odd') user_id_type
    , value
  FROM tbl t
) a
group by a.category, a.user_id_type
;

CodePudding user response：

You can create a table according to your requirements and fill this table using a select like this:

INSERT INTO table_2
SELECT category, 
CASE WHEN user_id % 2 = 0 THEN 'even' ELSE 'odd' END user_id_type, 
SUM(value) FROM table_1 GROUP BY user_id_type, category ORDER BY category;

Please have a look here to see it's working: fiddle

CodePudding user response：

You can use MODulo to dete4rmin the odds

CREATE TABLE table_1 (
  `category` VARCHAR(2),
  `user_id` INT(2), 
  `value` DECIMAL(3,2)
 );
INSERT INTO table_1
(`category`, `user_id`, `value`)
VALUES
('A', 1, 0.01),
('A', 2, 0.05),
('A', 3, 0.09),
('A', 4, 0.12),
('B', 1, 0.34),
('B', 2, 0.27),
('B', 3, 0.08),
('B', 4, 0.12)
;

SELECT 
`category`
, MAX(CASE WHEN  MOD( `user_id`,2) = 1 then 'Even' ELSE 'Odd' ENd ) odds, SUM(`value`) 
FROM table_1
GROUP BY category, MOD( `user_id`,2)
ORDER BY category, MOD( `user_id`,2) ASC

category | odds | SUM(`value`)
:------- | :--- | -----------:
A        | Odd  |         0.17
A        | Even |         0.10
B        | Odd  |         0.39
B        | Even |         0.42

db<>fiddle here

CodePudding user response：

If you want to have rows for 'odd' and 'even' for all categories, even the ones that have only odd or only even user_ids (so that you get 0 in that case), then you need a CROSS join of the distinct categories to a query that returns 1 and 0 (the possible remainders when you divide an integer by 2) and a LEFT join to the table to aggregate:

SELECT c.category,
       CASE WHEN n.nr = 1 THEN 'odd' ELSE 'even' END user_id_type,
       COALESCE(SUM(t.value), 0) value_sum
FROM (SELECT DISTINCT category FROM table_1) c 
CROSS JOIN (SELECT 0 nr UNION ALL SELECT 1) n
LEFT JOIN table_1 t ON t.category = c.category AND t.user_id % 2 = n.nr
GROUP BY c.category, n.nr
ORDER BY c.category, n.nr DESC;

See the demo.