PostgreSQL | Combine queries results

Question

I have a "simple" situation here, but I couldn't resolve it by myself. So what I need is combine one result from the first query with the second one, to get a "final result".

The first query get the number of shots by player.

SELECT player, COUNT(shot) shots FROM table1 GROUP BY player;

+---------+-------+
| player  | shots |
+---------+-------+
| player1 | 10    |
+---------+-------+
| player2 | 10    |
+---------+-------+

The second is to get the hits.

SELECT player, COUNT(hit) hits FROM table2 GROUP BY player;

+---------+-------+
| player  | hits  |
+---------+-------+
| player1 | 10    |
+---------+-------+
| player2 | 5     |
+---------+-------+

And what I need is to calculate the accuracy (hits / shots * 100), displaying the result something like that.

+---------+-------+------+-----+
| player  | shots | hits | acc |
+---------+-------+------+-----+
| player1 | 10    | 10   | 100 |
+---------+-------+------+-----+
| player2 | 10    | 5    | 50  |
+---------+-------+------+-----+

Gordon Linoff · Accepted Answer · 2020-08-02 02:01:00Z

2

You can use join after aggregation:

SELECT player, s.shots, h.hits
FROM (SELECT player, COUNT(shot) as shots
      FROM table1
      GROUP BY player
     ) s JOIN
     (SELECT player, COUNT(hit) as hits
      FROM table2
      GROUP BY player
     ) h
     USING (player);

Do you really intend COUNT()? It seems that SUM() would be more appropriate.

Also, this only returns players in both tables. If you want players in either table, use FULL JOIN.

answered Aug 2, 2020 at 2:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bruno Oyama Over a year ago

Thx! This solution works for me. I have to use COUNT() because it's kind of log table, so each line counts as shot or hit.

Collectives™ on Stack Overflow

PostgreSQL | Combine queries results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related