Mysql group_concat with distinct values from another row

Question

My first post here, hope I am following the posting guidelines right. If not, please do point out.

My table structure (credit_notes) with some sample data looks like this:

index | customer_id | user_id | doc_number | doc_date   | amount | scheme_description
+-----+-------------+---------+------------+------------+--------+-------------------
1     | 8           | 2       | IABCD001   | 2012-09-08 | 1232   | XYZ Scheme
2     | 8           | 2       | IABCD001   | 2012-09-08 | 2032   | XYZ Scheme 
3     | 8           | 2       | IABCD002   | 2012-09-09 | 2556   | XYZ Scheme 
4     | 8           | 2       | IABCD003   | 2012-09-08 | 232    | XYZ Scheme 
5     | 8           | 2       | IABCD001   | 2012-09-10 | 2556   | ABC Scheme
6     | 8           | 2       | IABCD003   | 2012-09-10 | 232    | ABC Scheme

My requirement is to extract the total amount per scheme_description & doc_date combination, with grouping. This is my current query:

SELECT 
    SUM( `amount` ) AS `totalamount`,
    GROUP_CONCAT( CONCAT( `amount` , ";" ) ORDER BY `index` DESC SEPARATOR '' ) AS `amounts`, 
    GROUP_CONCAT( CONCAT( `doc_number` , ";" ) ORDER BY `doc_number` DESC SEPARATOR '' ) AS `docno` , 
    `doc_date` AS `docdate` , 
    `scheme_description` AS `schemedesc` 
FROM (
        SELECT 
            `c`.`index` ,
            `c`.`doc_number` , 
            `c`.`doc_date` , 
            `c`.`amount` , 
            `c`.`scheme_description` 
        FROM `credit_notes` AS `c` 
        WHERE `c`.`customer_id` = 8 
        AND `c`.`user_id` LIKE 2
)t
GROUP BY `scheme_description` , `doc_date`

This query returns this structure:

totalamount | amounts        | docno                       | docdate    | schemedesc
+-----------+----------------+-----------------------------+------------+-----------
3496        | 1232;2032;232; | IABCD001;IABCD001;IABCD003; | 2012-09-08 | XYZ Scheme
2556        | 2556;          | IABCD002;                   | 2012-09-09 | XYZ Scheme
23477       | 255;23222;     | IABCD007;IABCD007;          | 2012-09-10 | ABC Scheme

But, I need a slight change to this, I want to sum the amounts column for every distinct docno, and group it in the right order. So, as per the above data, the query should yield:

totalamount | amounts        | docno                       | docdate    | schemedesc
+-----------+----------------+-----------------------------+------------+-----------
3496        | 3264;232;      | IABCD001;IABCD003;          | 2012-09-08 | XYZ Scheme
2556        | 2556;          | IABCD002;                   | 2012-09-09 | XYZ Scheme
23477       | 23477;         | IABCD007;                   | 2012-09-10 | ABC Scheme

Is this possible using group_concat and distinct and sum. Read up all I could find, but could not find anything which pointed me to the right direction.

Is this possible?

fthiella · Accepted Answer · 2012-12-05 10:53:11Z

1

Your first query could be written as this:

select
  doc_date, scheme_description,
  group_concat(doc_number),
  group_concat(amount),
  sum(amount)
from
  credit_notes
group by doc_date, scheme_description

and your second:

select
  doc_date, scheme_description,
  group_concat(doc_number),
  group_concat(amount),
  sum(amount)
from (
  select doc_date, scheme_description, doc_number, sum(amount) as amount
  from credit_notes
  group by doc_date, scheme_description, doc_number
) s
group by doc_date, scheme_description

answered Dec 5, 2012 at 10:53

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

deadbeef Over a year ago

Realized the trick was to use two group by statements. Thank you for the clear and quick response. Really appreciate it.

Devart · Accepted Answer · 2012-12-05 10:57:24Z

0

Try this query -

SELECT
  SUM(amount) totalamount,
  GROUP_CONCAT(amount) amounts,
  GROUP_CONCAT(doc_number) docno,
  doc_date docdate,
  scheme_description schemedesc
FROM (
  SELECT scheme_description, doc_date, doc_number, SUM(amount) amount
    FROM credit_notes
    GROUP BY scheme_description, doc_date, doc_number) t
GROUP BY scheme_description, doc_date

Add WHERE, ORDER BY and other clauses if needed.

edited Dec 5, 2012 at 10:57

answered Dec 5, 2012 at 10:48

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

deadbeef Over a year ago

Aah, the trick was to use two group by statements. Thank you so very much.

Devart Over a year ago

Yes, it was. Because first time you need to group by three fields, second - by two fields.

Muhammad Raheel · Accepted Answer · 2012-12-05 11:05:10Z

0

Try this query

select 
    group_concat(doc_num) as DocNumm,
    group_concat(amount) as DocNumm,
    scheme_description as SchemaDesc,
    sum(amount) as TotalAmount,
    amount as Amount,
    doc_date as DocDate
from (
    SELECT * from `schema` 
) as g
group by SchemaDesc , DocDate

answered Dec 5, 2012 at 11:05

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

1 Comment

deadbeef Over a year ago

thanks for your response. However, this does not suffice, 2 group by statements will need to be used, with sum() at two stages. The other responses solved my problem.

Collectives™ on Stack Overflow

Mysql group_concat with distinct values from another row

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related