I am trying to get a list of donors who have cumulatively donated $5K between two different campaigns. My data is something like this

Attributes table

Donations table

Donor table

This is what I tried, but the total I am getting is not right at all.

WITH test AS (
    SELECT don.donorid,don.donationamount,a.attributevalue
    FROM attributes table a 
    INNER JOIN donations don ON don.transactionid=a.transactionid
) 
SELECT d.donorid,
       SUM(CASE WHEN test.attributevalue='campaign 1' OR test.attributevalue='campaign 2' 
       THEN test.donationamount END) AS campaing_donation,
       SUM(test.donationamount) AS total_donations
FROM donortable d
INNER JOIN test ON d.donorid = test.donorid
GROUP BY d.donorid
HAVING SUM(CASE WHEN test.attributevalue = 'campaign 1' OR test.attributevalue = 'campaign 2' THEN test.donationamount END) > 5000

but this is not working. My total donations sum is giving a value that is several times higher than the actual value.

Ideally, the final result would be something like this:

CodePudding user response：

Select
  sum (Donations.donationamount),
  donor.donorid,
  donor.name
from
    Attributes
join Donations on 
    Donations.transactionid = attributes.transactionid
Join Donor on
    donor.donorid = donations.donorid
Where
    Attribute.attributevalue in ('campaign 1','campaign 2')
Group by
    donor.donorid,
    donor.name

CodePudding user response：

create table #transection_tbl(tran_id int,attributevalue varchar(20))
create table #donation_tbl(tran_id int,donation_amount int ,donar_id int)


select donar_id,max(donation_amount) as 'campaing_amount',
sum(donation_amount) as 'totalamount'
from #transection_tbl as t1
inner join #donation_tbl as t2 on t1.tran_id=t2.tran_id
group by donar_id
having COUNT(attributevalue)=2