CODEWITHSUNDEEP
sqlpostgresqljoin
alexvassel
alexvassel

Reputation: 10740

SQL query (without subqueries)

I have three tables:

user(id, name);

tasks(id, user_id, text, date);

bonus(id, user_id, sum, date, type).

I'm trying to write a query that will give me a list of all users, quantity of their tasks and a sum of bonuses of every type, each in separate column.

There are three types of bonuses:

type = (1, 2, 3)

So, I mean a table, which looks like this

name | task_count | bonus_1 | bonus_2 | bonus_3

Upvotes: 1

Views: 250

Answers (3)

Andomar
Andomar

Reputation: 238048

Consider doing the aggregation before joining on user. For example:

select  u.name
,       t.task_count
,       b.bonus_1
,       b.bonus_2
,       b.bonus_3
,       t.comment_count
from    user u
left join
        (
        select  user_id
        ,       count(distinct t.id) as task_count
        ,       count(distinct c.id) as comment_count
        from    tasks t
        left join
                comment c
        on      c.task_id = t.id
        group by
                t.user_id
        ) t
on      t.user_id = u.id
left join
        (
        select  user_id
        ,       sum(case when type = 1 then sum end) as bonus_1
        ,       sum(case when type = 2 then sum end) as bonus_3
        ,       sum(case when type = 3 then sum end) as bonus_2
        from    bonus
        group by
                user_id
        ) b
on      b.user_id = u.id

Upvotes: 0

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

NOTE: the following won't work! Retained for information

SELECT
    user.name,
    COUNT(tasks.id) AS task_count,
    SUM(bonus1.sum) AS bonus_1,
    SUM(bonus2.sum) AS bonus_2,
    SUM(bonus3.sum) AS bonus_3
FROM user
    LEFT OUTER JOIN tasks ON tasks.user_id = user.id
    LEFT OUTER JOIN bonus1 ON bonus1.user_id = user.id
        AND bonus1.type = 1
    LEFT OUTER JOIN bonus2 ON bonus2.user_id = user.id
        AND bonus1.type = 2
    LEFT OUTER JOIN bonus3 ON bonus3.user_id = user.id
        AND bonus1.type = 3
GROUP BY
    user.name
ORDER BY
    user.name

You might need to handle NULL values specially if it is not the case that every user will always have at least one task and at least one of each type of bonus.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

SELECT u.name, 
        t.task_count, 
        SUM(CASE WHEN b.type=1 then b.sum END) AS bonus_1,
        SUM(CASE WHEN b.type=2 then b.sum END) AS bonus_2,
        SUM(CASE WHEN b.type=3 then b.sum END) AS bonus_3
FROM user u 
LEFT JOIN (SELECT user_id, COUNT(*) AS task_count 
           FROM tasks 
           GROUP BY user_id) t ON t.user_id = u.id
LEFT JOIN bonus b ON b.user_id = u.id 
GROUP BY u.id,u.name,t.task_count

Upvotes: 1

Related Questions