March 10, 2006 at 4:58 pm
Does anybody know how to do this?
SELECT
CASE
WHEN SHARE.BALANCE <=50 THEN '1.) <=$50'
WHEN SHARE.BALANCE >50 AND SHARE.BALANCE <=500 THEN '2.) $51-$500'
WHEN SHARE.BALANCE >500 AND SHARE.BALANCE <=1000 THEN '3.) $501-$1,000'
WHEN SHARE.BALANCE >1000 AND SHARE.BALANCE <=2000 THEN '4.) $1,001-$2,000'
WHEN SHARE.BALANCE >2000 AND SHARE.BALANCE <=5000 THEN '5.) $2,001-$5,000'
WHEN SHARE.BALANCE >5000 AND SHARE.BALANCE <=10000 THEN '6.) $5,001-$10,000'
WHEN SHARE.BALANCE >10000 AND SHARE.BALANCE <=50000 THEN '7.) $10,001-$50,000'
WHEN SHARE.BALANCE >50000 AND SHARE.BALANCE <=100000 THEN '8.) $50,001-$100,000'
ELSE '9.) $100,000+'
END AS Category , sum(balance) AS Balances
FROM SHARE
WHERE SHARE.CLOSEDATE IS NULL AND CHARGEOFFDATE IS NULL
GROUP BY
CASE
WHEN SHARE.BALANCE <=50 THEN '1.) <=$50'
WHEN SHARE.BALANCE >50 AND SHARE.BALANCE <=500 THEN '2.) $51-$500'
WHEN SHARE.BALANCE >500 AND SHARE.BALANCE <=1000 THEN '3.) $501-$1,000'
WHEN SHARE.BALANCE >1000 AND SHARE.BALANCE <=2000 THEN '4.) $1,001-$2,000'
WHEN SHARE.BALANCE >2000 AND SHARE.BALANCE <=5000 THEN '5.) $2,001-$5,000'
WHEN SHARE.BALANCE >5000 AND SHARE.BALANCE <=10000 THEN '6.) $5,001-$10,000'
WHEN SHARE.BALANCE >10000 AND SHARE.BALANCE <=50000 THEN '7.) $10,001-$50,000'
WHEN SHARE.BALANCE >50000 AND SHARE.BALANCE <=100000 THEN '8.) $50,001-$100,000'
ELSE '9.) $100,000+'
END
Output
Category Balances % of Total
1.) <=$50 944290.9100
2.) $51-$500 33121635.7400
3.) $501-$1,000 47693281.7200
4.) $1,001-$2,000 4047955.4700
5.) $2,001-$5,000 324131986.3500
6.) $5,001-$10,000 384745723.7800
7.) $10,001-$50,000 1509396843.2300
8.) $50,001-$100,000 1094177076.3400
9.) $100,000+ 1555261765.6600
March 10, 2006 at 5:34 pm
Make it a view and then modify view output as:
select v.Category, v.Balances, v.Balances/t.Total as [% of Total]
from
vwBalances v
cross join
(select sum(Balances) as Total from vwBalances) t
Cheers,
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply