cssc1
asked on
How to modify SQL to display percent on chart
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/(selec t 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/(sele ct 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
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/(selec
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/(sele
FROM CountOfRate
group by Definitions
--------------------------
Please advise
TunnelProjectSafetyAudit-VER2-DB.zip
Chart-1.jpg
Chart-2.jpg
Chart-3.JPG
ASKER
Please see attached error message
sorry...Error message?
ASKER
change
"group by definitions"
to
"group by definition"
"group by definitions"
to
"group by definition"
ASKER
Please see attached error message
error2.jpg
error2.jpg
ASKER
Anyone have any help?
ok...are you scripting these SQL statements in ASPrunner Pro? or in your data backend?
ASKER
scripting these SQL statements in ASPrunner Pro
ASKER
Wait, the ASP Runner does the SQL after I select the table and fields. So, I think this is scripting?
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.
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<]
FROM [>Project_Overview_Chart<]
Group by Definitions.
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.
Select
Definition,
Sum(CountOfRate) as sum_CountOfRate,
CountOfRate / (Select Sum(CountOfRate) from [>Project_Overview_Chart<]
FROM [>Project_Overview_Chart<]
Group by Definitions.
ASKER
Sorry, one more time. change definitions to definition. Needs to match line two.
How's it going? Did it work?
ASKER
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.
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.
ASKER
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
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
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.
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.
ASKER
Is there a setting on the pie chart that will give you percentages as a number?
NO
NO
ASKER
Well, we're closer...
Hold on...
Hold on...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi There.
Is it working?
Is it working?
ASKER
Wow!
Thanks!
Thanks!
Select
Definition,
Sum(CountOfRate) as sum_CountOfRate,
CountOfRate / (Select Sum(SountOfRate) from [>Project_Overview_Chart<]
FROM [>Project_Overview_Chart<]
Group by Definitions.