How to SUM using group_concat with group by on differents tables

user_13930024 0
Viewed 34

I have 3 tables: Users, Tools,tool_assigned:

User:

id name department
1 john a
2 peter b
3 paul a

Tools:

id name
1 tool1
2 tool2
3 tool3

Tool_assigned:

id id_user id_tool quantity
1 1 1 1
2 1 2 3
3 1 3 1
4 2 1 2
5 2 2 3
6 2 3 1
7 3 1 2
8 3 2 1
9 3 3 1

I need to get this result:

id_tool tool_name total_quantity summary_department
1 tool1 5 a-1, b-2, c-2
2 tool2 7 a-3, b-3, c-1
3 tool3 3 a-1, b-1, c-1

I've tried doing a query that can get that result, but the best I've gotten so far is this:

SELECT tool.id, tool.name, sum(tool_assigned.quantity) as total_quantity, 
       group_concat(user.deparment, '-', tool_asigned.quantity) as summary_department 
FROM tool_assigned 
INNER JOIN users ON tool_assigned.id_user = user.id 
INNER JOIN tools ON tool_assigned.id_tool = tool.id 
GROUP BY tools.name, users.department

Please help me, I've already tried everything.

aggregate-functions inner-join sum mysql sql
2 Answers

You need two levels of aggregation: first by tool and department, then by tool only. You could phrase this as:

select t.id, t.name as tool_name, 
    sum(x.quantity) as total_quantity,  
    group_concat(x.info order by x.department) as summary_department
from tools t
inner join (
    select ta.id_tool, u.department, concat(u.name, '-', sum(t.quantity)) as info 
    from tool_assigned ta 
    inner join users on ta.id_user = u.id 
    group by ta.id_tool, u.department
) x on x.id_tool = t.id
group by t.id
user_106767160

you can also use a with statment


with tools_per_dept as (
    SELECT users.department as dept,
           tool.id as tool_id,
           tool.name as tool_name,
           sum(tool_assigned.quantity) as total_quantity
    FROM tool_assigned
             INNER JOIN users ON tool_assigned.id_user = user.id
             INNER JOIN tools ON tool_assigned.id_tool = tool.id
    GROUP BY tools.name, users.department
)
select tool_id,
       tool_name,
       sum(total_quantity) as total,
       group_concat(dept, '-', total_quantity) as summary_department 
from tools_per_dept
group by tool_id, tool_name
user_15258670
Related