October 30, 2012 at 5:08 am
CREATE TABLE [dbo].[Acct_sum](
[Acct_no] [varchar](7) NULL,
[Total] int null
)
insert into Acct_sum(Acct_no,Total) values (11,100)
insert into Acct_sum(Acct_no,Total) values (12,200)
insert into Acct_sum(Acct_no,Total) values (13,10000)
insert into Acct_sum(Acct_no,Total) values (14,500)
insert into Acct_sum(Acct_no,Total) values (15,1000)
insert into Acct_sum(Acct_no,Total) values (16,1300)
insert into Acct_sum(Acct_no,Total) values (17,11100)
insert into Acct_sum(Acct_no,Total) values (18,1200)
I want the result to be populated on a pie chart
for Total < 100 and number of accounts
for Total > 100 and number of accounts
for Total > 1000 and number of accounts
for Total > 10000 and number of accounts
So basically I need a script which will transform the result of table in pie chart
for eg 5 account are greater than 100 and the total value is 900 something like that
So the column in the table should be
accountnumberCount TotalValue
October 30, 2012 at 5:51 am
??
October 30, 2012 at 6:03 am
Wow a whole 43 minutes and a second between bumping the thread. Please remember we are all unpaid volunteers here and post within our spare time, so please be patient and someone will eventually answer your question.
Based on what I believe the interpretation to be you want something like the following.
;with cte as
(
select
case
when total < 100 then '< 100'
when total >= 100 and total < 1000 then '>= 100 and < 1000'
when total >= 1000 and total < 10000 then '>= 1000 and < 10000'
when total >= 10000 then '>= 10000'
end as value,
total
from
Acct_sum
)
select
value,
COUNT(value) as NumberOfAccounts,
SUM(total) as Total
from
cte
group by
value
October 30, 2012 at 6:05 am
You'll need to combine CASE and GROUP BY, something like this:
SELECT CASE WHEN Total >= 0 AND Total < 100 THEN
WHEN Total >= 100 AND Total < 1000 THEN ...
END,
COUNT(Acct_no)
FROM Acct_sum
GROUP BY CASE WHEN ...
You don't state which side the boundaries fall into, so I've guessed - 0-99, 100-999 etc.
I'm sure you can work out the rest.
Cheers
Gaz
October 30, 2012 at 6:07 am
@anthony-2 - you wait a whole 43 minutes and then 2 come along at once! 🙂
October 30, 2012 at 6:18 am
Hey anthony thanks for the reply. The query works fine but I was just curious about the with clause as I have never used it before. can you through some light on it please. Thanks
October 30, 2012 at 6:20 am
Its the beginning of the CTE construct, and acts much like a derived table.
November 9, 2012 at 7:45 am
Sorry I am back again. I need to manipulate the order in which the value is displayed. Is it possible>?
November 9, 2012 at 7:47 am
Add in an order by clause sorting either Asc or Desc depending on which way round you want the values to be displayed.
November 9, 2012 at 8:07 am
when total < 100 then '< 100'
when total >= 100 and total < 1000 then '>= 100 and < 1000'
when total >= 1000 and total < 10000 then '>= 1000 and < 10000'
when total >= 10000 then '>= 10000'
doesnt get ordered properly. So I just used case statement again.
when total < 100 then 1
when total >= 100 and total < 1000 then 2
when total >= 1000 and total < 10000 then 3
when total >= 10000 then 4
and then used case again. 1,2,3,4 get ordered correctly. 🙂
November 9, 2012 at 8:11 am
Yep, thats varchar ordering for you as it orders left to right of the characteres in the string, not based on value, so yep doing it 1,2,3,4 would be the way to go.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply