shaddf Posted June 16, 2017 Share Posted June 16, 2017 I have this table (tblStats): +------+-----------+------+------------+----------+-------+------+ | P_id | ShortName | logo | SeasonName | SeasonID | Apps1 | Gls | +------+-----------+------+------------+----------+-------+------+ | 1 | Friendly | NULL | 2016/17 | 12 | 1 | 3 | | 1 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 2 | Friendly | NULL | 2016/17 | 12 | 1 | 1 | | 2 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 3 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 3 | NDIV V | NULL | 2016/17 | 12 | 1 | 1 | | 4 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 4 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 10 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 10 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 11 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 11 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 12 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 12 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | | 13 | Friendly | NULL | 2016/17 | 12 | 1 | 0 | | 13 | NDIV V | NULL | 2016/17 | 12 | 1 | 0 | +------+-----------+------+------------+----------+-------+------+ 16 rows in set, 1 warning (0.07 sec) I have this query ti get records for a particular P_id from the table above: select P_id,group_concat(ShortName),group_concat(Apps1),group_concat(Gls),SUM(Apps1) as t_Apps,sum(Gls)as t_Gls from (SELECT houseid as P_id, ShortName, logo, SeasonName, SUM(Apps) as Apps1, SUM(Gl) as Gls FROM tblStats where SeasonName='2016/17' GROUP BY P_id ; which outputs: +------+-------------------------+---------------------+-------------------+--------+-------+ | P_id | group_concat(ShortName) | group_concat(Apps1) | group_concat(Gls) | t_Apps | t_Gls | +------+-------------------------+---------------------+-------------------+--------+-------+ | 1 | Friendly,NDIV V | 1,1 | 3,0 | 2 | 3 | | 2 | Friendly,NDIV V | 1,1 | 1,0 | 2 | 1 | | 3 | NDIV V,Friendly | 1,1 | 1,0 | 2 | 1 | | 4 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 10 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 11 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | | 12 | NDIV V,Friendly | 1,1 | 0,0 | 2 | 0 | | 13 | Friendly,NDIV V | 1,1 | 0,0 | 2 | 0 | +------+-------------------------+---------------------+-------------------+--------+-------+ But the shorname is not in order as in Friendly,NDIV V.But there is an interchange as we go on.This will affect the data displayed how can I clear this? Link to comment Share on other sites More sharing options...
requinix Posted June 16, 2017 Share Posted June 16, 2017 If all you want to change is to have those items listed in order then notice that GROUP_CONCAT can sort too. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.