CODEWITHSUNDEEP
mysqlsqlgroup-bygroupingconcatenation
oren_isp
oren_isp

Reputation: 779

SQL - Group by certain field and concat another

I have a query that return the following table:

   P_id   S_id   Time
1  "20"     A    15 
2  "30"     B    50
3  "50"     A    99 
4  "70"     A    60

I want to group the table, based on the column "Sid", and sorted by Column "Time" so it will look like this:

        P_id       S_id   
1  "20","70","50"    A     
2       "30"         B

What is the best way to do this by changing the SQL query?

When trying just to add "GROUP BY S_id" I get the error:

SELECT list expression references column query which is neither grouped nor aggregated at [2:16]

(Meaning it doesn't know how to group the values of P_id (all strings)

Upvotes: 0

Views: 45

Answers (2)

Nikhil
Nikhil

Reputation: 3950

select group_concat(P_id,',') from tablename group by S_id ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think you want:

select s_id, group_concat(p_id order by time) as p_ids
from t
group by s_id;

If you want a first column that has numbers, you can add that in:

select (@rn := @rn + 1) as seqnum, s_id, group_concat(p_id order by time) as p_ids
from t cross join
     (select @rn := 0) params
group by s_id;

Upvotes: 1

Related Questions