CODEWITHSUNDEEP
mysqlsql
Marc-9
Marc-9

Reputation: 145

Find rows where sum of columns from multiple rows is greater than X

The best way to explain this question is through the real life example it would solve. I have two tables, Match Overview and Match Attributes

Match overview looks like

id, home_id, away_id, date, result

While match attributes looks like

id, match_id, attribute_id, attribute_value

Where match_id is a foreign key for id in the match overview table, and attribute id is the integer representation for an attribute like,

home_goals_full_time or total_yellow_cards or away_goals_extra_time

I want to do a query on match attributes to find all matches where the total number of goals scored was greater than X.

Since each attribute is a row of its own we need to find all rows that have an attribute id of {3,4,5,6} we return the match_id if the sum of 3 and 4 (home_goals_full_time + away_goals_full_time) is greater than X OR the sum of 5 and 6 (home_goals_extra_time + away_goals_extra_time) is greater than X.

One solution is to just add a total goals column to my match overview table and search that, however I wish to be able to do this for any of my attributes of which there are many and not every game has an attribute for, if I added a column to my match overview table for each attribute the table would be very wide and full of nulls.

Is it possible to make a query where I enter a value X and it returns all games where the sum of the attributes is greater (or less than) my X? or would this require multiple queries or potentially a redesign of my schema.

Upvotes: 0

Views: 448

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

Another straight-forward query for this task:

select * 
from match_overview m
where 
(
  select sum(ma.attribute_value)
  from match_attributes ma
  where ma.match_id = m.id
  and ma.attribute_id in (3, 4)
) > @X
or
(
  select sum(ma.attribute_value)
  from match_attributes ma
  where ma.match_id = m.id
  and ma.attribute_id in (5,6)
) > @X;

And yet another:

select * 
from match_overview m
where id in
(
  select match_id
  from match_attributes
  where attribute_id in (3, 4)
  group by match_id
  having sum(attribute_value) > @X
) 
or id in
(
  select match_id
  from match_attributes
  where attribute_id in (5, 6)
  group by match_id
  having sum(attribute_value) > @X
);

Upvotes: 0

ysth
ysth

Reputation: 98398

This is fairly straightforward:

select m.id
from match_overview m
join match_attributes ma on ma.match_id=m.id and ma.attribute_id in (3,4,5,6)
group by m.id
having sum(case when ma.attribute_id in (3,4) then ma.attribute_value end) > X
    or sum(case when ma.attribute_id in (5,6) then ma.attribute_value end) > X

Upvotes: 2

Related Questions