Concatenating a description together when using a Group By Clause

I've got two tables "acctg_invoice" and "acctg_invoice_item" in a 1 to many relationship. so i can have multiple invoice items linked to one invoice.

I need to use a "Group By" clause 开发者_运维问答to group the invoice items together and the SUM() function to add up the invoice item amounts that pertain to the invoice. Because I'm using the "Group By" the "Description" field only uses the last item description.

In the "acctg_invoice_item" I store the "description" of the item. For example, one item may say "Monthly Rent", and another item "Monthly Utilities" but both items belong to one "invoice_id which comes from the parent table "acctg_invoice".

I would like to contatenate all the items "descriptions" into one long string, such as "Monthly Rent, Monthly Utilities", etc. so my end result set would have only one line item that shows the "amount", and "description", but I'm clueless how to do this.

Is this possible in SQL?

I'm using MySql. Thanks in advance!

Here is a sample SQL statement:

SELECT sum(b.amount_curr) as amount, b.description
FROM acctg_invoice a, acctg_invoice_item b
WHERE a.acctg_invoice_id = b.acctg_invoice_id
GROUP BY a.acctg_invoice_id

Try this:

SELECT 
    SUM(b.amount_curr) amount, 
    GROUP_CONCAT(b.description) descr
FROM acctg_invoice a INNER JOIN acctg_invoice_item b
ON a.acctg_invoice_id = b.acctg_invoice_id
GROUP BY a.acctg_invoice_id

You can use the GROUP_CONCAT() aggregate function for this. Here's an example:

SELECT
    SUM(b.amount_curr) AS amount,
    GROUP_CONCAT(B.description SEPERATOR ', ') AS description
FROM
    acctg_invoice A
INNER JOIN acctg_invoice_item B ON
    B.acctg_invoice_id = A.acctg_invoice_id
GROUP BY
    A.acctg_invoice_id

There are additional options for the GROUP_CONCAT() aggregate function, so you can also define what order they should be concatenated in, etc. The documentation is available on the MySQL website.

Please do not use implicit SQL '89 join syntax it's an anti-pattern, use explicit join syntax instead.
Your queries will be easier to maintain, debug and understand.

SELECT sum(item.amount_curr) as amount
       , GROUP_CONCAT(item.description) as descriptions
FROM acctg_invoice inv
INNER JOIN acctg_invoice_item item 
        ON (inv.acctg_invoice_id = item.acctg_invoice_id)
GROUP BY inv.acctg_invoice_id WITH ROLLUP

If you add with rollupto your group by clause you'll get a grand total at the bottom.

Links
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html