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

user_18787192 0
Viewed 74

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)
;
mysql sql
3 Answers

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

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

user_187948260

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

user_51935360
Related