Skip to content
Advertisement

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

While match attributes looks like

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,

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.

Advertisement

Answer

This is fairly straightforward:

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement