phpmyadmin Question posted in
The official documentation can be found here.

SUM different queries – Phpmyadmin

Good afternoon everyone,

For a project on my internship, I am making a dashboard for the game we play in the break. We play darts, FIFA & billiards.

For example: 

SELECT pw.name AS `winner`
     , COUNT(*) AS total 
  FROM billiard_games g 
  JOIN players p1 
    ON p1.id = g.player_1 
  JOIN players p2 
    ON p2.id = g.player_2 
  LEFT 
  JOIN billiard_winners w 
    ON w.id = g.id 
  LEFT 
  JOIN players pw 
    ON pw.id = w.winner 
 GROUP 
    BY winner

That outpust the following result:

Winner | Total
---------------
Name1 | 5
Name2 | 7

That means name1 won 5 times and name2 won 7 times. So far so good?

I have different queries for the other games. On the query above, I am asking for billiard_games. The other queries are the same but instead of billiard_games, I am asking dart_games & fifa_game.

On the front page of the application, I want a big overview. With the all time winners. This table need to count ALL the wins of one player. So the wins of FIFA, Billiard & Darts all togheter.

I tried this:

SELECT
(SELECT pw.name AS `winner`, COUNT(*) AS total FROM billiard_games g INNER JOIN players p1 ON p1.id = g.player_1 INNER JOIN players p2 ON p2.id = g.player_2 LEFT JOIN billiard_winners w ON w.id = g.id LEFT JOIN players pw ON pw.id = w.winner GROUP BY winner)+
(SELECT pw.name AS `winner`, COUNT(*) AS total FROM dart_games g INNER JOIN players p1 ON p1.id = g.player_1 INNER JOIN players p2 ON p2.id = g.player_2 LEFT JOIN dart_winners w ON w.id = g.id LEFT JOIN players pw ON pw.id = w.winner GROUP BY winner)+
(SELECT pw.name AS `winner`, COUNT(*) AS total FROM fifa_games g INNER JOIN players p1 ON p1.id = g.player_1 INNER JOIN players p2 ON p2.id = g.player_2 LEFT JOIN fifa_winners w ON w.id = g.id LEFT JOIN players pw ON pw.id = w.winner GROUP BY winner)
AS SumCount

This however doesn’t work. Can anyone help me? I want ALL the names and ALL the wins in one view.

Tags:

3

Answers


  1. 0

    Please try this.

    SELECT pw.name AS 'winner', COUNT(pw.name) AS total 
FROM billiard_games g 
INNER JOIN players p1 
ON p1.id = g.player_1 
INNER JOIN players p2 
ON p2.id = g.player_2 
LEFT JOIN billiard_winners w 
ON w.id = g.id 
LEFT JOIN players pw ON pw.id = w.winner GROUP BY winner
    Login or Signup to reply.
  2. 0

    You can try using UNION ALL

    select winner, sum(total)
from
(
SELECT pw.name AS `winner`, COUNT(*) AS total 
FROM billiard_games g INNER JOIN players p1 ON p1.id = g.player_1 INNER JOIN players p2 ON p2.id = g.player_2 LEFT JOIN billiard_winners w ON w.id = g.id LEFT JOIN players pw ON pw.id = w.winner GROUP BY winner
union all
SELECT pw.name AS `winner`, COUNT(*) AS total FROM dart_games g INNER JOIN players p1 ON p1.id = g.player_1 INNER JOIN players p2 ON p2.id = g.player_2 LEFT JOIN dart_winners w ON w.id = g.id LEFT JOIN players pw ON pw.id = w.winner 
GROUP BY winner
union all
SELECT pw.name AS `winner`, COUNT(*) AS total FROM fifa_games g INNER JOIN players p1 ON p1.id = g.player_1 INNER JOIN players p2 ON p2.id = g.player_2 LEFT JOIN fifa_winners w ON w.id = g.id LEFT JOIN players pw ON pw.id = w.winner 
GROUP BY winner
) A group by winner
    Login or Signup to reply.
  3. 0

    Quite complex explanation though in order to run your query remove + and try to use union like Select * from (<your_query_with_+changed_to_union>) to atleast run it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search