php - Grouping results by name and points -

data

+----+-------+------+--------+----+----+----------+------------+----------+----------+ | id | chess | name | groupc | c2 | c3 | section  | grouppoint | c2points | c3points | +----+-------+------+--------+----+----+----------+------------+----------+----------+ |  1 | c100  | s1   |      1 |  0 |  1 | section1 |          5 |        0 |        3 | |  2 | c100  | s2   |      1 |  0 |  1 | section1 |          5 |        0 |        5 | |  3 | c100  | s3   |      1 |  1 |  1 | section1 |          5 |        5 |        1 | |  4 | c101  | s4   |      0 |  1 |  0 | section3 |          0 |        3 |        0 | |  5 | c102  | s5   |      0 |  1 |  0 | section1 |          0 |        3 |        0 | |  6 | c103  | s6   |      1 |  1 |  5 | section2 |          1 |        1 |        0 | |  7 | c103  | s7   |      1 |  1 |  0 | section2 |          1 |        0 |        0 | |  8 | c103  | s8   |      1 |  0 |  3 | section2 |          1 |        0 |        5 | |  9 | c105  | s9   |      0 |  0 |  0 | section1 |          0 |        0 |        0 | | 10 | c104  | s10  |      1 |  1 |  0 | section3 |          3 |        3 |        0 | | 11 | c104  | s11  |      1 |  0 |  0 | section3 |          3 |        0 |        0 | | 12 | c104  | s12  |      1 |  1 |  1 | section3 |          3 |        1 |        3 | | 13 | c107  | s13  |      1 |  1 |  1 | section1 |          3 |        1 |        1 | | 14 | c107  | s14  |      1 |  1 |  1 | section1 |          3 |        1 |        1 | | 15 | c107  | s15  |      1 |  1 |  1 | section1 |          3 |        1 |        1 | | 16 | c105  | s16  |      1 |  1 |  0 | section3 |          0 |        5 |        0 | | 17 | c105  | s17  |      1 |  1 |  0 | section3 |          0 |        5 |        0 | | 18 | c105  | s18  |      1 |  1 |  0 | section3 |          0 |        5 |        0 | | 19 | c109  | s19  |      0 |  1 |  0 | section3 |          1 |        0 |        0 | +----+-------+------+--------+----+----+----------+------------+----------+----------+ 

problem

i find total points of section. if value chess same: name , grouppoint/c2points/c3points (depending on groupc/c2/c3) should grouped.

i'm using following query don't know how grouping name , grouppoints:

select * ( select s1.id, s1.name, 'competition 1' competition,  s1.grouppoint  points students s1 section='section1' ,    ifnull(s1.groupc,'')<>'' , s1.grouppoint<>0  union select s2.id, s2.name, 'competition 2' competition, s2.c2points points students s2 s2.section='section1' , ifnull(s2.c2,'')<>'' ,      s2.c2points<>0 union select s3.id, s3.name, 'competition 3' competition, s3.c3points points students s3 s3.section='section1' , ifnull(s3.c2,'')<>'' , 3.c3points<>0 )t order t.points desc, t.competition asc 

please refer sqlfiddle

preferred output

the question is: how group results name/points final output like:

 +----------+---------------+--+--------------+--------+  | section1 |               |  |              |        |  +----------+---------------+--+--------------+--------+  | id       | name          |  | competition  | points |  | 1        | s1, s2, s3    |  | competition1 | 5      |  | 3        | s3            |  | competition2 | 5      |  | 2        | s2            |  | competition3 | 5      |  | 15       | s13, s14, s15 |  | competition1 | 3      |  | 5        | s5            |  | competition2 | 3      |  | 1        | s1            |  | competition3 | 3      |  | 15       | s15           |  | competition2 | 1      |  | 13       | s13           |  | competition2 | 1      |  | 14       | s14           |  | competition2 | 1      |  | 14       | s14           |  | competition3 | 1      |  | 15       | s15           |  | competition3 | 1      |  +----------+---------------+--+--------------+--------+ 

if add group chess, grouppoint you're able concatenate names using group_concat()

i've created simplified example should want (i omitted union simplicity):

simplified query

select   id,   group_concat(distinct name order name asc separator ", ") name,   "competition 1" competition,   grouppoint points students section = "section1" , groupc not null , grouppoint <> 0 group chess, grouppoint 

output

+----+---------------+---------------+--------+ | id | name          | competition   | points | +----+---------------+---------------+--------+ | 1  | s1, s2, s3    | competition 1 | 5      | | 13 | s13, s15, s15 | competition 1 | 3      | +----+---------------+---------------+--------+ 

see sqlfiddle

other remarks

1. you seem have error in sql: ifnull(s3.c2,'')<>'', mean: ifnull(s3.c3,'')<>''
2. the correct way ifnull(<field>, '')<>'' <field> not null

final query

taking above, think final query should this:

select * ( select s1.id, group_concat(s1.name order s1.name asc separator ', ') name, 'competition 1' competition, s1.grouppoint points students s1 section='section1' , s1.groupc not null , s1.grouppoint<>0 group s1.chess, s1.grouppoint union select s2.id, group_concat(s2.name order s2.name asc separator ', ') name, 'competition 2' competition, s2.c2points points students s2 s2.section='section1' , s2.c2 not null , s2.c2points<>0 group s2.chess, s2.c2points union select s3.id, group_concat(s3.name order s3.name asc separator ', ') name, 'competition 3' competition, s3.c3points points students s3 s3.section='section1' , s3.c3 not null , s3.c3points<>0 group s3.chess, s3.c3points )t order t.points desc, t.competition asc 

see sqlfiddle