I can not figure out what request to do for my purposes:

Table

 
 match_id com_n count
 1597 1 2
 1597 2 3
 1598 1 3
 1598 2 3
 1599 1 2
 1599 2 4
 ... ... ...

The first column is the match id. The second is the team number. The third is the number of heads washed. For example, in the first line In the match with id 1597, the first team scored 2, and the second 3 goals.

Request

We need such a query, which will display the number of none. For example the match with id 1598 was a draw.

Thank you in advance!

  • 3
    If it is guaranteed that for each match there are strictly 2 entries (no more and no less), then no double group by is needed. group by match id and check having max(count)=min(count) maximum number in the group can be equal to the minimum only if you have a draw - Mike
  • 2
    If it is guaranteed that for each match there are strictly 2 entries. This can be traced by HAVING ... AND COUNT(*) = 2 (and in a good way, we must also check that there are two teams, and not one two times) ... For it’s considered “kosyachny” matches are absolutely not necessary - Akina
  • Thank you very much. Almost always there are 2 records for a match, but sometimes there is only 1 record (that is, only one team scored). But no more than two! These teams work, but I don’t understand what it means (and in an amicable way, we still need to check that there are two teams, not one or two times) ... But it seems he understood. If there are 2 records for one match, then they are guaranteed to be different teams. You can make out the answer. - Panteleimon Akakkov
  • sometimes there is only 1 record (that is, only one team scored). Yeah ... and zero entries, if none of the teams scored ... no? By the way, with such parsley, MIN / MAX can’t be done anymore ... If there are 2 records for one match, are these different teams guaranteed? Is this supported by the appropriate constraint? Or so, like "software will not allow"? so - I guarantee, will allow. - Akina
  • Yes. But there are simply no such records. That is, 0-0 matches information is lost in the table. Such draws are not found by these teams, but I do not need it! - Panteleimon Akakkov 1:01 pm

1 answer 1

Double grouping - separated group by n,m a comma group by n,m

I.e

  select a, b, count(*) ct from table1 group by com_n,count

Well, we want a draw - here

  select a,b, count(*) ct from table1 where a = b group by a,b

It remains a trifle - bring the table to score1 = a and score2 = b.

  select a.count, b.count, count(*) ct from table2 a inner join table2 b on a.match_id = b.match_id and a.com_n <> b.com_n where a.count = b.count group by a.count, b.count

Under "normal" conditions (there are no duplicates, build-ups, and so on), the result will be good. You can add grouping by match - then it will be known what kind of idi matches won.

If the answer is one number ... stop ... And now the joke is grouping ... heaving in this case is not needed at all. I looked at the monster above, and decided to "cut down" the group ... so

  select * from table2 a inner join table2 b on a.match_id = b.match_id and a.com_n <> b.com_n and a.count = b.count

Or one number - replacing * with count(*)