SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql query pie chart


sql query pie chart

Author
Message
freecoder
freecoder
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 142
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
freecoder
freecoder
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 142
??
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24942 Visits: 6519
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7824 Visits: 6046
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
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7824 Visits: 6046
@Anthony - you wait a whole 43 minutes and then 2 come along at once! :-)
freecoder
freecoder
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 142
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24942 Visits: 6519
Its the beginning of the CTE construct, and acts much like a derived table.

Using Common Table Expressions



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


freecoder
freecoder
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 142
Sorry I am back again. I need to manipulate the order in which the value is displayed. Is it possible>?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24942 Visits: 6519
Add in an order by clause sorting either Asc or Desc depending on which way round you want the values to be displayed.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


freecoder
freecoder
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 142
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. Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search