We help IT Professionals succeed at work.

How to modify SQL to display percent on chart

cssc1
cssc1 asked
on
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

TunnelProjectSafetyAudit-VER2-DB.zip
Chart-1.jpg
Chart-2.jpg
Chart-3.JPG
Comment
Watch Question

Evan CutlerVolunteer Chief Information Officer

Commented:
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.

Author

Commented:
Please see attached error message
Evan CutlerVolunteer Chief Information Officer

Commented:
sorry...Error message?
Evan CutlerVolunteer Chief Information Officer

Commented:
change
"group by definitions"
to
"group by definition"

Author

Commented:
Please see attached error message
error2.jpg

Author

Commented:
Anyone have any help?
Evan CutlerVolunteer Chief Information Officer

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

Author

Commented:
scripting these SQL statements in ASPrunner Pro

Author

Commented:
Wait, the ASP Runner does the SQL after I select the table and fields. So, I think this is scripting?
Evan CutlerVolunteer Chief Information Officer

Commented:
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.
Evan CutlerVolunteer Chief Information Officer

Commented:
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.

Author

Commented:
Evan CutlerVolunteer Chief Information Officer

Commented:
Sorry, one more time.  change definitions to definition.  Needs to match line two.
Evan CutlerVolunteer Chief Information Officer

Commented:
How's it going?  Did it work?

Author

Commented:
Not good.

Please see image
Error-12-12-2011.JPG
Evan CutlerVolunteer Chief Information Officer

Commented:
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.

Author

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

I will show, with images, how ASPRunnel creates the chart.
Chart-1.JPG
Chart-2.JPG
Chart-3.JPG
Chart---4.JPG
Chart---5JPG.JPG
Evan CutlerVolunteer Chief Information Officer

Commented:
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.

Author

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

NO
Evan CutlerVolunteer Chief Information Officer

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

 Slide 1 Slide 2 Slide 3
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.

Author

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

See image
Missing-Definations.JPG
Evan CutlerVolunteer Chief Information Officer

Commented:
Well, we're closer...
Hold on...
Volunteer Chief Information Officer
Commented:
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.
Evan CutlerVolunteer Chief Information Officer

Commented:
Hi There.
Is it working?

Author

Commented:
Wow!
Thanks!