Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql query pie chart Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 5:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 6:21 AM
Points: 93, 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

Post #1378691
Posted Tuesday, October 30, 2012 5:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 6:21 AM
Points: 93, Visits: 142
??
Post #1378706
Posted Tuesday, October 30, 2012 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1378714
Posted Tuesday, October 30, 2012 6:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1378716
Posted Tuesday, October 30, 2012 6:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
@Anthony - you wait a whole 43 minutes and then 2 come along at once!
Post #1378718
Posted Tuesday, October 30, 2012 6:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 6:21 AM
Points: 93, 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
Post #1378724
Posted Tuesday, October 30, 2012 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1378725
Posted Friday, November 09, 2012 7:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 6:21 AM
Points: 93, Visits: 142
Sorry I am back again. I need to manipulate the order in which the value is displayed. Is it possible>?
Post #1383017
Posted Friday, November 09, 2012 7:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
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

Post #1383019
Posted Friday, November 09, 2012 8:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 6:21 AM
Points: 93, 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. :)
Post #1383036
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse