codeigniter 4 mysql query distinct or groupby-CodePudding

I'm using codeigniter-4.1.9. I have a score table and there have multiple round. each user getting there scores in different rounds. I want to retrieve each users score in a single row with different rounds but unable to get expected result. How to fix this?

I tried both :

$scmodel->query("SELECT DISTINCT * FROM score GROUP BY score");

$scmodel->query("SELECT DISTINCT * FROM score GROUP BY teamid");

expected Result should:

teamid round1 round2 round3
 1       20       30      40
 2       50       30      90
 3       45       38      70

But Getting:

teamid round1 round2 round3
  1      20
  2      50
  3      45
  1              30
  2              30
  3              38
  1                       40
  2                       90
  3                       70

Score Table are

CodePudding user response：

If you want the rounds as separate columns you need to make a pivot query something like this:

$scmodel->query("
  SELECT teamid, 
  SUM(IF(roundid = 1, score, 0)) as round1,
  SUM(IF(roundid = 2, score, 0)) as round2,
  SUM(IF(roundid = 3, score, 0)) as round3
FROM score
GROUP BY teamid
");

SAMPLE DATA:

create table score (id integer(3), 
                   teamid varchar(100), 
                   jid integer(3), 
                   roundid integer(3), 
                   challengeid integer(3), 
                   score integer(5), 
                   mailstatus integer(3)
                   );

insert into score values
  (1, 'CTO202241400-GOLD', 2, 2, 4, 89, 1),
  (2, 'CTO20220171-DIAM', 2, 1, 3, 75, 1),
  (3, 'CTO-2022-1133224-ZOOM', 2, 1, 4, 87, 1),
  (4, 'CTO20221888-EAGL', 2, 2, 4, 76, 1),
  (5, 'CTO2022889-ABGR', 2, 3, 5, 59, 1),
  (6, 'CTO202241400-GOLD', 2, 3, 5, 89, 1),
  (7, 'CTO20221888-EAGL', 2, 3, 5, 97, 1),
  (8, 'CTO-2022-1133224-ZOOM', 2, 3, 5, 67, 1),
  (9, 'CTO20220171-DIAM', 2, 3, 5, 98, 1);

OUTPUT:

| teamid                | round1 | round2 | round3 |
|-----------------------|--------|--------|--------|
| CTO-2022-1133224-ZOOM | 87     | 0      | 67     |
| CTO20220171-DIAM      | 75     | 0      | 98     |
| CTO20221888-EAGL      | 0      | 76     | 97     |
| CTO202241400-GOLD     | 0      | 89     | 89     |
| CTO2022889-ABGR       | 0      | 0      | 59     |

SQL Fiddle: http://www.sqlfiddle.com/#!9/7ed3fa/3/0