trying to get grandtotal in sql query

[sap1958]

sum(case when gifttype in ('b','g','y') then giftjntamt else 0 end)as Gifts,
sum(case when gifttype = 'c' then giftjntamt else 0 end)as Credits

sample output
Gifts Credits
25 0
55 25
250 100

I know want to get a total of the gifts and credits and show it in a separate column. I then want to use the grand total and say that if the grand total is less than 250, suppress it

--sum(Gifts)+(Credits) as GrandTotal

 

[bborissov]

.....
    sum(case when gifttype in ('b','g','y') 
                  then giftjntamt 
             else 0 end)as Gifts,
   sum(case when gifttype = 'c' 
                 then giftjntamt
            else 0 end)as Credits
....
FROM .....
WHERE .....
GROUP BY .......
HAVING SUM(giftjntamt) > 249
....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[drtree]

This is totally random, but are you using fund-raising software (possibly made by JSI/Sage/Best)? The reason I ask is because I spent many years writing queries just like that for folks in a Development Office and it brought back some memories...

 

[markros]

Alternative solution to Borislav:

select Gifts, Credits, Gifts+Credits as GrandTotal from (select 
sum(case when gifttype in ('b','g','y')                   then giftjntamt              else 0 end)as Gifts,   sum(case when gifttype = 'c'                  then giftjntamt            else 0 end)as Credits....FROM .....WHERE .....GROUP BY .......) X
where Gifts+Crdits >=250

PluralSight Learning Library