How to write a mysql query for this requirement?

I have one table like below.

I want to get the total number of books donated by donation_organization_group and distinct by donation_pledge.

I wrote the sql query like below.

select count(DISTINCT donation_pledge), sum(DISTINCT donation_no_of_books) from e25_donations WHERE donation_organization_group =”4-H Club”

By this query i am getting the result as 6 | 1248, in this result 96, 72 is skipping, but i want to skip only the repeating pledge.I want to get the total of 72 + 120 + 96 + 96 + 960 +72. Some one please help me.

Answer

You need to select the DISTINCT values first, and then COUNT and SUM them:

select count(donation_pledge), 
       sum(donation_no_of_books), 
       count(distinct donation_organization_name)
from (select distinct donation_pledge, 
             donation_no_of_books, 
             donation_organization_name
      FROM e25_donations 
      WHERE donation_organization_group ="4-H Club") d