CODEWITHSUNDEEP
mysqlsqldatabaseviewmariadb
José Manuel
José Manuel

Reputation: 23

MariaDB/mysql SQL query question: I want a partial sum of a column based on the group values of another column

It's difficult (and long) for me to explain what I need, so I'll do with an example. I have the following table structure:

CREATE TABLE example(
year CHAR(4) NOT NULL,
group   CHAR(4) NOT NULL DEFAULT '',
subgroup    CHAR(7) NOT NULL DEFAULT '',
items_1 INT(11),
set_item_1  INT(11),
items_2 INT(11),
set_item_2  INT(11),
PRIMARY KEY (year,group,subgroup)

);

And the next sample data:

year group subgroup items_1 set_item_1 items_2 set_item_2
2021 G001 G001-001 2 0 2 1
2021 G001 G001-002 5 1 2 1
2021 G001 G001-003 5 1 3 2
2021 G001 G001-004 5 1 3 2
2021 G001 G001-005 1 0 3 2
2021 G002 G002-001 3 2 4 3
2021 G002 G002-002 3 2 4 3
2021 G003 G003-001 7 0 1 0
2020 G001 G001-001 9 0 2 4
2020 G001 G001-002 5 0 2 4
2020 G001 G001-003 4 0 4 0
2020 G002 G002-001 8 3 1 0
2020 G002 G002-002 8 3 2 0
2020 G002 G002-003 8 3 3 0

I want to implement a view (result_view) that returns this result for the former data:

SELECT * FROM result_view;
year group sum_items_1 sum_items_2
2021 G001 8 5
2021 G002 3 4
2021 G003 7 1
2020 G001 18 6
2020 G002 8 6 
set_item_1 = 0 means individual value of items_1 to sum 

set_item_2 = 0 means individual value of items_2 to sum 

set_item_1 > 0 means set n (all values of items_1 in the same n set are the same), we sum only one value of items_1 of set_item_1 = n 

set_item_2 > 0 means set m (all values of items_2 in the same m set are the same), we sum only one value of items_2 of set_item_2 = m

So the previous sum_items_1 and sum_items_2 of result_view are calculated based on the next formula:

year group sum_items_1 sum_items_2
2021 G001 2 + 5 + 1 2 + 3
2021 G002 3 4
2021 G003 7 1
2020 G001 9 + 5 + 4 2 + 4
2020 G002 8 1 + 2 + 3

I have no idea what is the best way of doing this, any help will be apreciated.

I only have achive to split the problem into two queries (that would be mix), one for calculating set_items_1 = 0 and another for set_items_1 > 0 groups , but I don't know if this approach will be useful.

SELECT year,group,
    sum(items_1) AS sum_items_1
FROM example
WHERE set_item_1=0
group by year,group;

SELECT DISTINCT set_item_1, year, group,items_1 as sum_items_1
FROM example
WHERE set_item_1>0
group by DISTINCT set_item_1, year, group;

NOTE: In the last query we should sum all items_1 values for each set_item_1 of the same year and group later.

Upvotes: 0

Views: 176

Answers (1)

Akina
Akina

Reputation: 42641

SELECT  `year`, `group`,
        SUM(DISTINCT CASE WHEN set_item_1 THEN items_1 ELSE 0 END)
      + SUM(CASE WHEN set_item_1 THEN 0 ELSE items_1 END) sum_items_1,
        SUM(DISTINCT CASE WHEN set_item_2 THEN items_2 ELSE 0 END)
      + SUM(CASE WHEN set_item_2 THEN 0 ELSE items_2 END) sum_items_2
FROM example
GROUP BY `year`, `group`
ORDER BY `year` DESC, `group` ASC;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=015fb64321afe1dff716d9c3e2d21aaf

Upvotes: 1

Related Questions