Make table with two new columns that categorize another column by even or odd and that sums all even and odd numbers in a category

Question

I have the following structure of table:

category	user_id	value
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

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:

category	user_id_type	value_sum
A	odd	0.10
A	even	0.17
B	odd	0.42
B	even	0.39

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

Answer 1

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

Answer 2

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
;

Answer 3

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