How to slice data (groupped by row counts)

[hugebreakthrough]

I have data like this:

ID | Name | Points |
1 | Ronnie | 20 |
3 | Rene |22 |
6 | Tyrza |30 |
7 | Lynda |44 |
10| Edwin |10 |
11| Eddy | 77 |
12| Toni | 10 |

How to query so I can have result like this (groupped by 2s):

Ronnie, Rene | 42
Tyrza, Lynda | 74
Edwin, Eddy | 87
Toni | 10

Please help. Thanks.

 

[hvass]

You need to create a sequence number then use this to group into sets of 2. There are a couple of ways of generating a sequence number either use auto_increment field or use a variable and increment it yourself @counter:=@counter+1

This means you can do something like this

drop table if exists stuff;
create table stuff
  (
    id int(8) unsigned, 
    name varchar(20), 
    points int(8) unsigned
  );
insert into stuff values
  (1 , 'Ronnie' , 20 ),
  (3 , 'Rene' ,22 ),
  (6 , 'Tyrza' ,30 ),
  (7 , 'Lynda' ,44 ),
  (10, 'Edwin' ,10 ),
  (11, 'Eddy' , 77 ),
  (12, 'Toni' , 10 );

select 	
  @c:=0,
  concat(max(if(b.r%2>0,name,'')),max(if(b.r%2=0,concat(',',name),''))) as names,
  sum(a.points) as points 
from stuff a inner join 
  (select 
    id,
    points,
    @c:=@c+1 as r 
   from stuff) b using(id) 
group by ceiling(b.r/2);

 

[tsuji]

[0] Am sure hvass is onto something, but cannot run it as such to produce any sensible results. Maybe hvass can take a look of it again.

[1] This is my take of the sql problem as the op forumulated. I use the table creation as sketched above.
[tt]
drop table if exists [blue]srctbl[/blue];
create table [blue]srctbl[/blue] (
id int(8) unsigned,
name varchar(20),
points int(8) unsigned
);

insert into [blue]srctbl[/blue] values
(1,'Ronnie',20),
(3,'Rene',22),
(6,'Tyrza',30),
(7,'Lynda',44),
(10,'Edwin',10),
(11,'Eddy',77),
(12,'Toni',10);

SELECT
CONCAT(IFNULL(CONCAT(t1.name,', '),''), t2.name) AS fullname,
IFNULL(t1.points,0)+t2.points AS fullscore
FROM
(
SELECT *
FROM
(
SELECT
@r:=@r+1 AS r
,id
,name
,points
FROM [blue]srctbl[/blue] AS t01
JOIN
(
SELECT @r:=0
) AS t02
) AS t03
WHERE mod(r,2)=0
) AS t1
RIGHT JOIN
(
SELECT *
FROM
(
SELECT
@r:=@r+1 AS r
,id
,name
,points
FROM [blue]srctbl[/blue] AS t01
JOIN
(
SELECT @r:=0
) AS t02
) AS t03
WHERE mod(r,2)=1
) AS t2
ON
t1.r=t2.r+1;
[/tt]