Clicky

The support people from xlinesoft when asked how to make a chart display percentages is below:
I do not know how to modify the sql query to display percentages?

To calculate percentage modify your SQL query in the following way:
-----------------
SELECT
FieldName1,
sum(FieldName2) as sum_FieldName2,
sum(FieldName2)*100/(select sum(FieldName2) from TableName) as percents
FROM products
group by FieldName1

The following table creates this chart:
Click_Here

-------------------------------------------------------

The following fields from the Click_Here table create this chart:
CountOfRate
Definition

---------------------------------------------------------

The code I tried that does not work:

SELECT
Definition,
sum(CountOfRate) as sum_CountOfRate,
sum(CountOfRate)*100/(select sum(CountOfRate) from Click_Here) as percents
FROM CountOfRate
group by Definitions

--------------------------------

Please advise

asked 12/06/2011 04:13

cssc1's gravatar image

cssc1 ♦♦


26 Answers:
Try This:

Select
Definition,
Sum(CountOfRate) as sum_CountOfRate,
CountOfRate / (Select Sum(SountOfRate) from [>Project_Overview_Chart<]) * 100 as Percentage
FROM [>Project_Overview_Chart<]
Group by Definitions.
link

answered

arcee123's gravatar image

arcee123

Please see attached error message
link

answered 2011-12-06 at 12:26:14

cssc1's gravatar image

cssc1

sorry...Error message?
link

answered 2011-12-06 at 15:56:34

arcee123's gravatar image

arcee123

link

answered 2011-12-06 at 17:31:15

cssc1's gravatar image

cssc1

change
"group by definitions"
to
"group by definition"
link

answered 2011-12-06 at 18:37:32

arcee123's gravatar image

arcee123

Please see attached error message
link

answered 2011-12-06 at 20:51:48

cssc1's gravatar image

cssc1

Anyone have any help?
link

answered 2011-12-07 at 03:39:33

cssc1's gravatar image

cssc1

ok...are you scripting these SQL statements in ASPrunner Pro? or in your data backend?
link

answered 2011-12-10 at 12:33:13

arcee123's gravatar image

arcee123

scripting these SQL statements in ASPrunner Pro
link

answered 2011-12-10 at 19:23:22

cssc1's gravatar image

cssc1

Wait, the ASP Runner does the SQL after I select the table and fields. So, I think this is scripting?
link

answered 2011-12-11 at 12:29:57

cssc1's gravatar image

cssc1

ok...I'm a retard...
hold on...

do this:
Change line three SountOfRate to CountOfRate...
And try again...

What you are doing is creating a SUM of Count of rate in an aggregation subquery.  If this doesn't work (after checking my spelling), we'll have to figure out the right way to do subquery aggregation for ASPRunner.

Select
Definition,
Sum(CountOfRate) as sum_CountOfRate,
CountOfRate / (Select Sum(SountOfRate) from [>Project_Overview_Chart<]) * 100 as Percentage
FROM [>Project_Overview_Chart<]
Group by Definitions.
link

answered 2011-12-11 at 12:30:57

arcee123's gravatar image

arcee123

This is the latest with that change:


Select
Definition,
Sum(CountOfRate) as sum_CountOfRate,
CountOfRate / (Select Sum(CountOfRate) from [>Project_Overview_Chart<]) * 100 as Percentage
FROM [>Project_Overview_Chart<]
Group by Definitions.
link

answered 2011-12-11 at 17:42:17

arcee123's gravatar image

arcee123

link

answered 2011-12-11 at 17:42:59

cssc1's gravatar image

cssc1

Sorry, one more time.  change definitions to definition.  Needs to match line two.
link

answered 2011-12-11 at 18:49:17

arcee123's gravatar image

arcee123

How's it going?  Did it work?
link

answered 2011-12-11 at 19:18:21

arcee123's gravatar image

arcee123

Not good.

Please see image
link

answered 2011-12-12 at 11:02:15

cssc1's gravatar image

cssc1

OK....Check to make sure the destination end is prepared for the columns you are trying to send it.

Instruction manuals indicate 3105 errors are because you have more/different columns than the distant end expects.

In this case you are trying to load the output of this select statement in a chart, yes?
if so, does the grid for the back end of the chart have the updated schema?  In this case three fields:
Definition,
sum_CountOfRate,
Percentage

If not, please make changes.
link

answered 2011-12-12 at 11:55:29

arcee123's gravatar image

arcee123

There is no grid on the backend.
I don't understand the explianation.

I will show, with images, how ASPRunnel creates the chart.
link

answered 2011-12-12 at 12:08:20

cssc1's gravatar image

cssc1

ok...but if you are using a PIE chart, you cannot have three fields.
Right now you have three fields rolling in that SQL Statement.

A PIE chart only uses two.
Is there a setting on the pie chart that will give you percentages as a number?

Let me dig this further...give me a couple of hours.
link

answered 2011-12-12 at 12:30:07

arcee123's gravatar image

arcee123

Is there a setting on the pie chart that will give you percentages as a number?

NO
link

answered 2011-12-12 at 13:34:31

cssc1's gravatar image

cssc1

ok..
Try This....
Return your SQL back the way it came, then follow the slides below

 
Slide1.GIF
  • 136 KB
  • Slide 1
Slide1.GIF

 
Slide2.GIF
  • 107 KB
  • Slide 2
Slide2.GIF

 
Slide3.GIF
  • 163 KB
  • Slide 3
Slide3.GIF


I want to say that it's a setting in the PIE Chart that adds the percentage for you.
It worked here as Slide 3 shows.

Give that a shot.
    link

    answered 2011-12-12 at 13:41:07

    arcee123's gravatar image

    arcee123

    It gave % but no definitions, (Excellent, .....

    See image
    link

    answered 2011-12-13 at 06:29:01

    cssc1's gravatar image

    cssc1

    Well, we're closer...
    Hold on...
    link

    answered 2011-12-13 at 17:04:27

    arcee123's gravatar image

    arcee123

    ok, sorry I took so long.
    I didn't want to do this.  I was hoping that ASPRunner had mentalities to do this for you...but I guess not.

    Revert back to start when you brought this into Expert-exchange (every setting I told you...go back).

    And change your SQL to this:
    SELECT
    CountOfRate,
    Definition & ' - ' & (CountOfRate * 100 / (Select Sum(CountOfRate) From [>Project_Overview_Chart<])) & '%' as Definition
    FROM [>Project_Overview_Chart<]
    GROUP BY Definition, CountOfRate

    It works.
    link

    answered 2011-12-13 at 18:33:12

    arcee123's gravatar image

    arcee123

    Hi There.
    Is it working?
    link

    answered 2011-12-14 at 09:33:08

    arcee123's gravatar image

    arcee123

    Wow!
    Thanks!
    link

    answered 2011-12-15 at 10:07:05

    cssc1's gravatar image

    cssc1

    Your answer
    [hide preview]

    Follow this question

    By Email:

    Once you sign in you will be able to subscribe for any updates here

    By RSS:

    Answers

    Answers and Comments

    Tags:

    ×132

    Asked: 12/06/2011 04:13

    Seen: 270 times

    Last updated: 12/15/2011 05:29