CODEWITHSUNDEEP
mysqlsql
SaidbakR
SaidbakR

Reputation: 13554

Concat sql sum in MySQL

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:

enter image description here

I need first row to be something like:

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

Upvotes: 2

Views: 5901

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

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;

Upvotes: 3

Related Questions