I have a sample table with some records. I show the table structure and the expected output. Please help me to do this. Now i use Group_Concat but its showing 1,1 and 2,2 and 3,3 in ids field.
ID PRODUCT QUANTITY 1 COMPUTER 100 2 MOUSE 120 3 KEYBOARD 10 4 MOUSE 150 5 KEYBOARD 300 6 COMPUTER 20 7 KEYBOARD 1 Eexpected Output
ID PRODUCT QUANTITY IDS 1 COMPUTER 120 1,6 2 MOUSE 270 2,4 3 KEYBOARD 311 3,5,7 Try like this:
select min(id) ID, product, sum(quantity), group_concat(id) IDS from table1 group by product order by min(id); ORDER BY ID(outer) will show you the first id for a product,
DISTINCT in group_concat compels all ids to be distinct and ORDER BY makes them in order
try this:
SELECT ID, PRODUCT, SUM(QUANTITY) QUANTITY, GROUP_CONCAT(DISTINCT ID ORDER BY ID) IDS FROM <table> GROUP BY PRODUCT ORDER BY ID ; Use sum for total quantity, with group by product and then order by id
Example:
select product, sum( quantity ) as quantity , group_concat( id ) as ids from products group by product order by id ; 
