how to group results

[shaddf]

how can i get all tha apps of each player from this table by seasonid for a particular ShortName (tournament)?eg all the Apps for houseid 12  in friendly in 1 column and NDIV V in another etc.Not tjat seasons can be different

+---------+-----------+------+----------+------------+------+----+
| houseid | ShortName | logo | SeasonID | SeasonName | Apps | Gl |
+---------+-----------+------+----------+------------+------+----+
|       5 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      18 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      13 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       6 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       1 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      14 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       7 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       2 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      15 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       8 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       3 | NDIV V    | NULL |       12 | 2016/17    |    1 |  1 |
|      16 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       9 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       4 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      17 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      10 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       5 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      18 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      11 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       6 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      12 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       7 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|      13 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       8 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       1 | Friendly  | NULL |       12 | 2016/17    |    1 |  3 |
|      14 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|       9 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       2 | Friendly  | NULL |       12 | 2016/17    |    1 |  1 |
|      15 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      10 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       3 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      16 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      11 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
|       4 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      17 | Friendly  | NULL |       12 | 2016/17    |    1 |  0 |
|      12 | NDIV V    | NULL |       12 | 2016/17    |    1 |  0 |
+---------+-----------+------+----------+------------+------+----+

i have tried this but itgets me 1 row only:

 

SELECT houseid as P_id, ShortName, logo, SeasonName, SUM(Apps) as Apps1, SUM(Gl) as Gls FROMtble stats;

Edited June 9, 2017 by shaddf

[Psycho]

Use GROUP BY. You need to group by all the unique fields - i.e. those that are not included in aggregate functions such as SUM()

 

SELECT houseid as P_id, ShortName, logo, SeasonName, SUM(Apps) as Apps1, SUM(Gl) as Gls
FROM tble_stats
GROUP BY houseid, ShortName, logo, SeasonName

 

You state you want the values for each houseid and Shortname. But, you also have logo and SeasonName - so you would need to group by those as well in case they can be different for the same houseid/Shortname values. If they cannot be different - then your structure is wrong.