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
;
