SQL count and sum some row

Question

I am getting a calculation error in my code

SELECT dep_id,
   dept_info.name AS dept_name,
   count(dep_id) AS totalInovators,
   count(user_id) AS totalIdea,
   sum(POINT) AS totalpoint
FROM user_info
JOIN dept_info ON user_info.dep_id =dept_info.id
JOIN user_idea ON user_info.id=user_idea.user_id
GROUP BY dep_id
ORDER BY dep_id DESC

My output result:

Expected result:

With my table user_info :

My user_idea :

My dept_info :

Answer 1

Below the query that solve your problem:

select 
    user_info.dep_id, 
    dept_info.name,
    count(*) as totalInovators, 
    sum(ideas_count) as totalIdea,
    sum(point) as totalpoint
from
    -- first we aggregate table user_idea
    (select user_id, count(*) ideas_count from user_idea group by user_id) ideas
    -- and after join rest of required tables
    join user_info on ideas.user_id = user_info.id
    join dept_info on dept_info.id = user_info.dep_id
group by user_info.dep_id,  dept_info.name;

Working code here: SQLize.online

Answer 2

I suspect that you are joining along different dimensions. If so, a quick-and-easy solution uses count(distinct):

select d.id, d.name as dept_name, count(distinct u.id) as totalInovators,
       count(*) as totalIdea, sum(i.point) as totalpoint
from user_info u join
     dept_info d
     on u.dep_id = d.id join
     user_idea i
     on u.id = i.user_id
group by d.id 
order by d.id desc