'Mysql query to sum a quantity from another table and under its group name if needed

I have 2 tables.

items table

Groups table

I am trying to get this result

Result table

Im having trouble with this query, the quantities seem t0o large, this is what i have and its wrong. I want the items-title and group to concat if they are in the same row, and the qtys to be summed up. Thank you.

SELECT g.`groupname`, SUM(i.`qty`) as qty 
FROM `items` AS i 
INNER JOIN `groups` AS g 
WHERE i.`groupid` = g.`groupid` OR i.`rownum` > 0 
GROUP BY g.`rownum`

Edit:

This may help if i've not explained correctly

Further results example

mysql


Solution 1:[1]

Split this into two subqueries. One gets all the groups and their corresponding items using a left join. The other gets the items that have no corresponding group. Then combine them with UNION.

SELECT g1.id, CONCAT_WS(' ', g1.groupname, g2.titles) AS groupname, g1.qty + IFNULL(g2.qty, 0) AS qty
FROM (
    SELECT g.id, g.groupname, SUM(qty) AS qty
    FROM groups AS g
    JOIN items AS i ON g.id = i.groupid
    GROUP BY i.id
) AS g1
LEFT JOIN (
    SELECT rownum, GROUP_CONCAT(title SEPARATOR ' ') AS titles, SUM(qty) AS qty
    FROM items
    WHERE rownum IS NOT NULL
    GROUP BY rownum
) AS g2 ON g1.id = g2.rownum

UNION ALL

SELECT i.id, i.title, SUM(qty) AS qty
FROM items AS i
LEFT JOIN groups AS g ON g.id = i.groupid
WHERE g.id IS NULL AND i.rownum IS NULL
GROUP BY i.id

I haven't tested this. If it doesn't work and you'd like me to debug it, either create a db-fiddle or post the sample data as text so I can copy and paste it.

Solution 2:[2]

Although this is not the exact solution, I can work with this inside my web page...

https://www.db-fiddle.com/f/viFL7xnknyZodpchPHtBqp/3

SELECT g.rownum, groupname AS name, IFNULL(sum(qty), 0) AS qty, g.groupid
FROM groups AS g
LEFT JOIN items AS i ON g.groupid = i.groupid
WHERE (g.groupid = i.groupid AND i.rownum = 0) 
OR (NOT EXISTS (SELECT 1 FROM items AS i2 WHERE i2.groupid = g.groupid) AND g.rownum > 0)

GROUP BY g.rownum

UNION ALL

SELECT rownum, title AS name, qty, groupid
FROM items
WHERE rownum > 0 

ORDER BY rownum

Close Solution monitor data

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Victor Lee

Related Questions