MySQL Query for total

Question

I have three tables

I have executed this query:

SELECT 
`tbl_players`.`id` AS `player_id`
,`tbl_players`.`player_name`
,`tbl_sports`.`sports_name`
,`tbl_scores`.`score`
FROM `tbl_players`
LEFT JOIN `tbl_scores` ON `tbl_players`.`id`=`tbl_scores`.`player_id`
LEFT JOIN `tbl_sports` ON `tbl_players`.`sports_id`=`tbl_sports`.`sports_id`
ORDER BY `tbl_players`.`id` ASC

And I got this:

Now I want this:

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — James, Feb 05 '19 at 06:20

score 1 · Answer 1 · answered Feb 05 '19 at 06:25

use conditional aggregation

select player_id,player_name,sports_name,
       sum(case when season=1 then score end) as season1_score,
       sum(case when season=2 then score end) as season2_score,
       sum(case when season=3 then score end) as season3_score
from
(
SELECT 
`tbl_players`.`id` AS `player_id`
,`tbl_players`.`player_name`
,`tbl_sports`.`sports_name`
,`tbl_scores`.`score`,`tbl_scores`.`season`
FROM `tbl_players`
LEFT JOIN `tbl_scores` ON `tbl_players`.`id`=`tbl_scores`.`player_id`
LEFT JOIN `tbl_sports` ON `tbl_players`.`sports_id`=`tbl_sports`.`sports_id`
)A group by player_id,player_name,sports_name

score 1 · Accepted Answer · answered Feb 05 '19 at 06:26

use conditional aggregation

 SELECT 
    `tbl_players`.`id` AS `player_id`
    ,`tbl_players`.`player_name`
    ,`tbl_sports`.`sports_name`
    , sum(case when tbl_scores.season=1 then `tbl_scores`.`score` end) as season1score
   ,sum(case when tbl_scores.season=2 then `tbl_scores`.`score` end) as season2score
,sum(case when tbl_scores.season=3 then `tbl_scores`.`score` end) as season3score
    FROM `tbl_players`
    LEFT JOIN `tbl_scores` ON `tbl_players`.`id`=`tbl_scores`.`player_id`
    LEFT JOIN `tbl_sports` ON `tbl_players`.`sports_id`=`tbl_sports`.`sports_id`
group by `tbl_players`.`player_name`
    ,`tbl_sports`.`sports_name`,player_id

score 0 · Answer 3 · answered Feb 05 '19 at 06:28

This might help.

You need to PIVOT your data

SELECT 
 player_id,
 player_name,
 sports_name,
 sum(case when tbl_scores.season=1 then `tbl_scores`.`score` end) as season1score,
 sum(case when tbl_scores.season=2 then `tbl_scores`.`score` end) as season2score,
 sum(case when tbl_scores.season=3 then `tbl_scores`.`score` end) as season3score
from 
  tbl_players
left join  tbl_sports 
  on `tbl_players`.`id`=`tbl_scores`.`player_id`
left join sports 
  on `tbl_players`.`sports_id`=`tbl_sports`.`sports_id`
group by
 layer_id,
 player_name,
 sports_name,

score -1 · Answer 4 · answered Feb 05 '19 at 06:22

-1

Use “distinct” selection to not get duplicates.

Just add “DISTINCT” right after “SELECT” in your query.

Example: SELECT DISTINCT column1, column2, ... FROM table_name;

answered Feb 05 '19 at 06:22

Chao1920

155
1
8

That’s doesn’t return what it’s asked. Indeed that’s not related at all. – James Feb 05 '19 at 06:24

score -1 · Answer 5 · answered Feb 05 '19 at 06:22

-1

I believe you should use inner join instead of left join

answered Feb 05 '19 at 06:22

Bharat Sharma

1
1

MySQL Query for total

5 Answers5