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.

Answer

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