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

Question

I have 2 tables.

I am trying to get this result

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

Can there be multiple items with the same rownum? If so, which one is concatenated with the group name? — Barmar, May 23 '22 at 21:35
You need a full outer join, since you have groups with no matching `items.rownum`, and you have `items.rownum` with no matching groups. See https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql?noredirect=1&lq=1 — Barmar, May 23 '22 at 21:37
there can be multiple items with the same rownum, they are to be concat in the items output, like in rownum 1 'GROUP1 EA' If items are in a group, they wont also be in a rownum of their own, but will be joined into the groups rownum — Jay204, May 23 '22 at 22:19
For the output, i want to show items and also groups on the same row. The groups contain the sum of the items quantities for that group, if that makes sense — Jay204, May 23 '22 at 22:23
now im wondering if i should of just made the items groupid the groupname and removed the group table altogether, this seems far simpler at the minute — Jay204, May 23 '22 at 22:30
But what if one of the items with `rownum=1` has `Title = EA`, and the other has `Title = XY`. What would be in the result, `GROUP1 EA XY`? — Barmar, May 23 '22 at 23:41
if XY in items had rownum=1, then yes. it is not in a group and would join in the result rownum 1. So 'GROUP1 EA XY', GROUP1 being the collective of BA, BB, BC. It doesnt show them, but uses their quantities. — Jay204, May 24 '22 at 00:21

Barmar · Answer 1 · 2022-05-25T02:59:14.613

0

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.

edited May 25 '22 at 02:59

answered May 24 '22 at 04:16

Barmar

741,623
53
500
612

Thanks, I will give this a try later on today – Jay204 May 24 '22 at 09:24
ive set a db fiddle https://www.db-fiddle.com/f/gFXY36bxXDWr9ytrzKdmrL/1 ive tried the code and also tried to make it work but i dont understand the error – Jay204 May 24 '22 at 21:25
I forgot `GROUP BY` in the last part of the query. – Barmar May 25 '22 at 02:59
I fixed that, but the results aren't correct. https://www.db-fiddle.com/f/gFXY36bxXDWr9ytrzKdmrL/2 I don't have time to debug it right now, sorry. – Barmar May 25 '22 at 03:00
no worries, ive tried my own version, whilst i get the qty's rightm the rownum and names are wrong https://www.db-fiddle.com/f/vTgpiaxuyiYi1FkNrPgjzw/4 – Jay204 May 25 '22 at 23:09

score 0 · Answer 2 · edited May 28 '22 at 19:23

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

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

2 Answers2