sql query pie chart

  • 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

  • ??

  • 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

  • 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

  • @anthony-2 - you wait a whole 43 minutes and then 2 come along at once! 🙂

  • 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

  • Its the beginning of the CTE construct, and acts much like a derived table.

    Using Common Table Expressions

  • Sorry I am back again. I need to manipulate the order in which the value is displayed. Is it possible>?

  • Add in an order by clause sorting either Asc or Desc depending on which way round you want the values to be displayed.

  • 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. 🙂

  • 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