Concat sql sum in MySQL

Question

Is there any way to use the sum yield in group_concat or any work around?

I have the following query, which returns redundant results for for item_id and items.title fields:

SELECT
  item_id, sum(qty), items.title, units.title
  FROM invoice_items
  LEFT JOIN items
   ON item_id = items.id
    LEFT JOIN units ON invoice_items.unit_id = units.id
 GROUP BY item_id, invoice_items.unit_id ORDER BY items.id, units.weight DESC;

I tried to use group_concat to concat redundant rows as follows:

SELECT
sum(qty),  group_concat(item_id, items.title, sum(qty) SEPARATOR '-')
  FROM invoice_items
  LEFT JOIN items
   ON item_id = items.id
    LEFT JOIN units ON invoice_items.unit_id = units.id
 GROUP BY item_id, invoice_items.unit_id ORDER BY items.id, units.weight DESC;

However, it returns this error: Invalid use of group function

What I want is to concatenate the sum yield in one row regard less of any other fields.

The following is a screen shot for the query result:

I need first row to be something like:

item_id: 1
sum(qty): 2, 13, 5
title: اسبوسيد أقراص

General GROUP BY tip: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh
– jarlh, Commented Jan 15, 2016 at 15:37
I guess removing invoice_items.unit_id from group by will fix the problem. Why have you used invoice_items.unit_id in group by — Pரதீப்
– Pரதீப், Commented Jan 15, 2016 at 15:39
sum() result is only available after all the relevant rows/groups have been discovered, which means it's NOT available at the time mysql is starting to concatenate the rows it's finding. — Marc B
– Marc B, Commented Jan 15, 2016 at 15:39
alternatively, you could concat in some sentinel value, e.g. **SUMGOESHERE** and then do a string replacement on the eventual concatted result, and substitute in your sum() value. — Marc B
– Marc B, Commented Jan 15, 2016 at 15:42

Gordon Linoff · Accepted Answer · 2016-01-15 15:43:43Z

3

Just do another group by using your query (with appropriate column names as a subquery):

SELECT item_id, title, GROUP_CONCAT(sumqty)
FROM (SELECT ii.item_id, sum(qty) as sumqty, i.title
      FROM invoice_items ii LEFT JOIN
           items i
           ON ii.item_id = i.id LEFT JOIN
           units u
           ON ii.unit_id = u.id
     GROUP BY ii.item_id, ii.unit_id
    ) ii
GROUP BY item_id;

answered Jan 15, 2016 at 15:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

6 Comments

SaidbakR Over a year ago

Excellent! It works so fine, However, I wonder, where is AS keyword! Thank you very much

SaidbakR Over a year ago

This is a screen shot of the result of this great query: here

spencer7593 Over a year ago

The AS keyword is optional when assigning a table alias. My preference is to always omit the AS keyword for a table alias. That's informed mostly by my experience with other databases, such as Oracle, which prohibit the AS keyword in that context. (When assigning an alias to a column, the AS keyword is also optional. My preference is to always include the AS keyword when assigning an alias to a column.) Gordon's query follows this same pattern.

Gordon Linoff Over a year ago

@spencer7593 . . . You exactly capture the rules that I use. Specifically, as for column aliases helps avoid the problem of missing commas (in the sense that select col1 col2 doesn't look right because it should be either select col1 as col2 or select col1, col2). I haven't found any use at all for as for table aliases.

spencer7593 Over a year ago

@GordonLinoff: exactly my reasoning AS well. I do reserve the right to be anal about capitalizing keywords. One small quibble with your query, re-using the same table alias ii. Once as for a table reference in the inline view, and again as the alias for the inline view. That's valid SQL. And the MySQL optimizer follows the rules. My personal preference is to not re-use a table alias within a statement.

|

Collectives™ on Stack Overflow

Concat sql sum in MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related