CODEWITHSUNDEEP
mysqlgroup-concat
user3085540
user3085540

Reputation: 295

Group Concat in MySql

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

Upvotes: 1

Views: 65

Answers (3)

avisheks
avisheks

Reputation: 1180

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
;

Upvotes: 0

G one
G one

Reputation: 2729

Try like this:

select min(id) ID, product, sum(quantity), group_concat(id) IDS
from table1
group by product
order by min(id);

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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
;

Upvotes: 0

Related Questions