SUM of a column in Join Query in SQL

Question

I have three tables.

fundraisers

    fundraiserId fundDescription fundName 
        14          testingfund     test      
        15          analysis        testing

fundraisingusers

     fundraisinguserId amount fundraiserId userId
        1               1000    14          12
        2               2000    14          13
        3               5000    15          14

users

    userId  firstName
     12       xyz
     13       pqr
     14       abc

I am trying to display all fundraisers from fundraisers table with sum of amount from fundraisingusers table and user details from user table.I am writing query something like this.

    SELECT f.fundraiserId as fundraiserId,
    f.fundDescription as fundDescription,
    f.fundName as fundName,
    sum(fu.amount as amount),
    fu.userId as userId FROM fundraisers as f 
    left outer join fundraisingusers as fu on f.fundraiserId =
    fu.fundraiserId left outer join users as u on fu.userId =
    u.userId";

Now i am able to get all the fundraisers with fundnames,fundDescriptions.But in amount i am not able to get the sum.

For example in fundraisingusers table i have two rows with same fundraiserId.Therefore in query as i am giving condition ON f.fundraiserId =fu.fundraiserId it is displaying fundraiser with fundraiserId 14 twice in the output with amounts showing empty.Can anyone tell how can i return unique rows from fundraising table if it has same fundraiserId more than once and also return sum of the amounts from fundraisingusers table with same fundraiserId.

My expected output is something like this.

    fundraiserId fundDescription fundName   amount userId
       14         testingfund      test      3000    
       15         analysis         testing   5000

I want to display all fundraisers.If same fundraiserId is there then i want to display it only once with amount field added.In userId column if more than one users donate for same fundraiser then same fundraiser should display with sum of amounts.

You need to use group by clause of sql. Group by fundraiser id and sum its amount. — Safi Mustafa
– Safi Mustafa, Commented Oct 6, 2017 at 11:57
But i want to display fundraiser only once.Now it is displaying twice — Sachin HR
– Sachin HR, Commented Oct 6, 2017 at 11:59
If you want a total amount broken down by fundraiserId, how would the userId fit into that? If you list all the individual users the sum of the amount would not be for the fundraiser but only for the specific user for a given fundraiser. user join not mandatory is confusing as both are different ways of breaking down the data. What is it you want? The total amount per fundraiser or the total amount per user in each fundraiser? — Nope
– Nope, Commented Oct 6, 2017 at 12:14
@SachinHR If you are summing the amount per user you will see duplicate fundraiser entries as there is multiple users per fundraiser. If you only want the amount per fundraiser you need to remove the user from your query and only sum the amount by fundraisers. — Nope
– Nope, Commented Oct 6, 2017 at 12:25

Nope · Accepted Answer · 2017-10-06 13:04:02Z

2

If you are looking for a total amount per fundraiser you can do it similar to the below:

select 
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName, 
  sum(fru.amount) as amount
from fundraisers as fr
join fundraisingusers fru on fru.fundraiserId = fr.fundraiserId
group by
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName

Output

|fundraiserId  |  fundDescription  |  fundName  |  amount |  
|---------------------------------------------------------|
|14            |  testingfund      |  test      |  3000   |
|15            |  analysis         |  testing   |  5000   |

See SqlFiddle Demo

However, if you are looking for a total amount per user per fundraiser then you can do it similar to this:

select 
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName, 
  sum(fru.amount) as amount,
  u.firstName
from fundraisers as fr
join fundraisingusers fru on fru.fundraiserId = fr.fundraiserId
join users as u on u.userId = fru.userId
group by
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName,
  u.firstName

Output

| fundraiserId | fundDescription | fundName | amount | firstName |
|----------------------------------------------------------------|
| 15           | analysis        | testing  | 5000   | abc       |
| 14           | testingfund     | test     | 2000   | pqr       |
| 14           | testingfund     | test     | 1000   | xyz       |

See SqlFiddle Demo

I want to display only fundraisers with status=1 like where f.status='1'.Where i have to place this code?

If you like to add a where clause you can add it before the group by, similar to:

select 
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName, 
  sum(fru.amount) as amount
from fundraisers as fr
join fundraisingusers fru on fru.fundraiserId = fr.fundraiserId
where fr.status = 1
group by
  fr.fundraiserId, 
  fr.fundDescription, 
  fr.fundName

See SqlFiddle Demo with Where Clause

edited Oct 6, 2017 at 13:04

answered Oct 6, 2017 at 12:07

Nope

22.4k8 gold badges50 silver badges73 bronze badges

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

11 Comments

Sachin HR Over a year ago

Can u tell if i can use where clause in this sql query for specifying condition?

Nope Over a year ago

@SachinHR Shouldn't be a problem. Which conditions though?

Sachin HR Over a year ago

I want to display only fundraisers with status=1 like where f.status='1'.Where i have to place this code?

Nope Over a year ago

@SachinHR You can add it after the join, before the group by. I added an example. To apply the where clause to the user breakdown, just add it at the same location in the other query.

Sachin HR Over a year ago

yup working ..thank you very much.The answers you gave and in the manner how you gave were really helpful and understandable

|

ScaisEdge · Accepted Answer · 2017-10-06 11:59:30Z

0

You need gruop by

  SELECT f.fundraiserId as fundraiserId,
    f.fundDescription as fundDescription,
    f.fundName as fundName,
    sum(fu.amount as amount),
    fu.userId as userId 
    FROM fundraisers as f 
    left outer join fundraisingusers as fu on f.fundraiserId = fu.fundraiserId 
    left outer join users as u on fu.userId =     u.userId
    group by  f.fundraiserId as fundraiserId,
    f.fundDescription as fundDescription,
    f.fundName as fundName,   
    fu.userId as userId ";

answered Oct 6, 2017 at 11:59

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Jibin Balachandran · Accepted Answer · 2017-10-06 12:00:39Z

0

Join fundraisers table with fundraisingusers table and do a COUNT(*).

SELECT fr.fundraiserId ,fr.fundDescription ,fr.fundName,count(*) AS amount
FROM fundraisers fr
INNER JOIN fundraisingusers fau ON fr.fundraiserId =fau.fundraiserId
GROUP BY fr.fundraiserId ,fr.fundDescription ,fr.fundName

answered Oct 6, 2017 at 12:00

Jibin Balachandran

3,4491 gold badge27 silver badges39 bronze badges

Comments

Yashrajsinh Jadeja · Accepted Answer · 2017-10-06 12:15:07Z

0

Another way around using inner query without a join, without a group by.

Select fundraiserId, fundDescription, fundName, (select sum(amount) from fundraisingusers where fundraisers.fundraiserId = fundraisingusers.fundraiserId) as amount  from fundraisers

answered Oct 6, 2017 at 12:15

Yashrajsinh Jadeja

1,7991 gold badge17 silver badges22 bronze badges

Collectives™ on Stack Overflow

SUM of a column in Join Query in SQL

4 Answers 4

11 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

11 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related