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 :

You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
– jarlh, Commented Aug 28, 2020 at 14:22
You are referencing a column called "point" but it looks like it should be "points". Also using SUM on the id column doesn't seem logical... did you perhaps mean to use COUNT there? — dazedandconfused
– dazedandconfused, Commented Aug 28, 2020 at 14:27
Most people here want sample table data and expected result as formatted text, not images. — jarlh
– jarlh, Commented Aug 28, 2020 at 15:06
I've completed a few tables but I don't know how to format a table in text — andreas
– andreas, Commented Aug 28, 2020 at 15:54

Slava Rozhnev · Accepted Answer · 2020-08-28 17:21:49Z

2

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

answered Aug 28, 2020 at 17:21

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-08-28 16:17:50Z

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

answered Aug 28, 2020 at 16:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL count and sum some row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related