Percentage Calculation In An Expression

Sep 20, 2007

I have a dataset named Diagnosis_Code_Analysis which has as part of the SQL query:
COUNT(DISTINCT IntOOS) AS Cases which is a simple case count. In my tabular report I have a column with the following expression: =Sum(Fields!Cases.Value) and this works fine.

Now, my next column I want to see the percentage of the whole for each case and I am using the following expression:
=Sum(Fields!Cases.Value)/Sum(Fields!Cases.Value, "Diagnosis_Code_Analysis")

My results are always 100% for each case (line item). I have tried changing the format to P and even 0.00% but it still reports 100%.

Can someone give me a better method to produce this percentage?

View 1 Replies


ADVERTISEMENT

Percentage Calculation Of Column

Aug 27, 2013

I have a column Bill_amount from bill_details table , i want to find 100%,30% of that column.

View 2 Replies View Related

T-SQL (SS2K8) :: Calculation For Each Value By A Percentage Using Round

Jul 23, 2014

I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values ​​for the percentage (also using round).

IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo
Create Table #Redondeo (Orden int Identity(1,1),Valores money)
Insert into #Redondeo
Select 71374.24 Union Select 16455.92
Union Select 56454.20 Union Select 9495.18

[Code] ...

RESULT: 177673.74; 37311.49

View 6 Replies View Related

Reporting Services :: Row Percentage Calculation In SSRS 2008 R2

May 11, 2015

I have a matrix in one of my SSRS report data is like below

Row as Branch
Column as Tasks (Progress, Completed , cancelled, In Complete)

Branch   Progress  Completed  cancelled  In Complete    Total    %(Complete/Total)
Aus           1             2               3                 4                    9                      22.2%
NZ            1             2               3                5                    11                      18.88%
US           0              0               3                 3               6                          0.00%

How can do in the SSRS matrix %(Completed/Total) percentage calculation ?

View 4 Replies View Related

Transact SQL :: Update Incoming Rows Based On Percentage Calculation

Apr 2, 2015

This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.

Requirements:

1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.

2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"

System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade Amount 

SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $      25,68,458.15

[Code] .....

View 36 Replies View Related

SQL Server 2008 :: Update Incoming Rows Based On Percentage Calculation

Mar 13, 2015

I have below records coming in from source files

ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S

In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.

I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.

ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT

The order of the records coming in can be different order, they need not be subsequent.

This is happening in SQl Server 2008.

View 3 Replies View Related

SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage

Dec 4, 2014

I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.

I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?

select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id

id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00

View 5 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But

Feb 8, 2007

Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1


"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"

Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works

However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur

(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)

View 4 Replies View Related

Reporting Services :: Running Value Expression Within Lookup Expression In SSRS?

Oct 28, 2015

I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".

I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.

Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.

This is the lookup expression that I added for the 2nd column.

=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").

I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.

(processing): (SortExpression ++ m_context.ExpressionType)

View 7 Replies View Related

Percentage

Mar 1, 2004

Hello
I need a little help.
I'm working on this query but is not returning the value I want

SELECT CONVERT(float, hits)/ abat as 'AVE'

The hit column value is 3 and abat 4 I want to return 750 but so far I'm getting
.75 or if I try decimal returns .7500000000

Thanks
Robert

View 2 Replies View Related

Percentage Help

May 26, 2006

I need to write a page that figures out the percentage of students passing an assessment item where students are from a particular school, grade, gender, and are not medically exempt.
I did a Count Statement on all test taker, and another Count Statement for the number passing the item who were not exempt.  Both of the statements are shown through seperate datalist controls.  I then want to get the percentage by dividing the number passed by the number taken and mulitplying that by 100. 
Is there a better technique or can I do this in a sql statement somehow?

View 1 Replies View Related

How Can I Get The Percentage ?

Mar 18, 2008

I am havingSELECT DISTINCT Name, (( count(id_Name) * 100 ) / (SELECT count(id_Name) AS tt FROM dbo.Name)) as PercentageFROM dbo.NameGROUP BY Namebut I get only integers : 1,0,4,12I need : 1.63, 0.4, 4.05, 12.78how can I do it ?is there a better way to calculate a percentage ?thank you

View 5 Replies View Related

Help With Percentage

Jun 3, 2008

Hi i create a temp table that count how many A,B,C and D there where in a table.

I need calculate the percentage.It could be easy put the problem is that i cannot devide by the same number.

Here my temp table DECLARE @Temp table
(
strLastName varchar(50),
strFirstName varchar(50),
strReponse char(10),
strNomObjet nvarchar(200),
strDescripteurOrdre char(10),
strNomDescripteur nvarchar(200),
strObjetOrdre char(10)
)

I want to check how many A there is.

SELECT strNomObjet,strNomDescripteur,count(*) FROM @Temp
WHERE strReponse = 'A'
GROUP BY strNomObjet,strNomDescripteur

This will give me somthing like this
For the first one there 25 A
Second 27 A

NOMBRE ET OPÉRATIONSdémontre une compréhension du concept du nombre25
NOMBRE ET OPÉRATIONSpeut faire les opérations sur les nombres pour résoudre des problèmes27
STATISTIQUES ET PROBABILITÉSpeut suivre les étapes d'une démarche statistique26
RÉGULARITÉS ET RELATIONSutilise des régularités (suites logiques) pour résoudre des problèmes24
RÉGULARITÉS ET RELATIONSutilise des relations mathématiques (algèbre) pour résoudre des problèmes23
FORMES ET ESPACE (géométrie)utilise la mesure pour résoudre des problèmes17
FORMES ET ESPACE (géométrie)utilise les figures géométriques pour résoudre des problèmes22
STATISTIQUES ET PROBABILITÉSutilise les probabilités pour résoudre des problèmes27
FORMES ET ESPACE (géométrie)utilise les transformations géométriques pour résoudre des problèmes22

After that i use


SELECT count(*) FROM @Temp
GROUP BY strNomObjet,strNomDescripteur

That will give me how many person have been tested.

It will give me this
36
36
35
36
35
36
36
35
35


So i need to take the first 25

25 / 36 * 100 = 69.4%
27 / 36 * 100 = 75%
26 / 35 * 100 = 74.2%


I hope you understand what i mean!
Any idea?
Tks in advance

View 8 Replies View Related

Top N Percentage

Jul 23, 2005

Hello!I have a need to create a function that will return a sublist ofelements from a table based on an associated column value and itsranking within the table (based on a percentage range).To expand with a simple example (only 4 elements):Element | Value---------------A102 | 5A506 | 10A322 | 15A342 | 20I would like to be able to return the range of elements that lie in the50-75% value range (e.g. third quartile range). Therefore with onlythese four elements I would want to return the element code 'A322'.I have attempted to achieve this by using SELECT TOP n PERCENT. I canbring back the required range by combining two select percentstatements.SELECT Element from ELEMENT_TABLEWHERE Element IN(SELECT TOP 75 PERCENT Element FROM ELEMENT_TABLE ORDER BY Value)AND Element NOT IN(SELECT TOP 50 PERCENT Element FROM ELEMENT_TABLE ORDER BY Value)The problem is that I cannot seem to pass a variable to this percentageso if I want another percentage range set, I ave to define anotherfunction.My question therefore is does anyone know either(1) a way I can pass a variable (n) to these SELECT n PERCENTstatementsOR(2) an alternative way of doing this?Many thanks!Andrew

View 3 Replies View Related

Percentage

Mar 11, 2008

Hi team, I have a column 'a' of a table in which i would like to calculate a percentge of all entries in that column 'a' and place the results in a column 'c' beside column 'a'. Can this be done in sql server ??

View 5 Replies View Related

What Percentage Of The Row Has Data

Sep 13, 2006

Lets say I have a table called ProductProductId Name Price Category1             Table  20.00  Furniture2             Chair3             Lamp            Misc What is the SQL command that would give me as a percentage the completeness of a row, either complete or empty?For e.g. SELECT ... FROM Product WHERE ProductId = 2 ; should give a result of 50% as half the Columns in that row are empty.e.g. SELECT ... FROM Product WHERE ProductId = 3 ; should give a result of 25% as 1/4 of the Columns in that row are empty.I tried used something like SELECT     COUNT(COLUMN_NAME) AS Expr1FROM         INFORMATION_SCHEMA.COLUMNSWHERE     (TABLE_NAME = 'Product')but dont know how to go forward.

View 2 Replies View Related

SQL Query To Get A Percentage

Mar 11, 2008

Ok guys, here is my query
GridSqlDataSource1.SelectCommand = "SELECT *, (([WinSum])/(([WinSum]) + ([LossSum]))) AS WinPercent FROM [ResultsView] WHERE ([CDIV] = @CDIV) ORDER BY [CTEAM]"
I need it to give me a 3 decimal percentage for WinPercent, but right now it is giving me 0 because as an example:
6 / 7 = 0 instead of 0.857
Any ideas?

View 10 Replies View Related

SQL Calculate Percentage

May 6, 2008

Hello every one, I am trying to get the total percentage of a column









Regions
Workbooks Required
Workbooks Sent 
Workbooks Returned
Workbooks Complete

A
20
21
20
18

B
33
33
33
30

C
19
29
18
16

D
9
18
8
8

Totals




Thanks in advance for any tips/solutions.

View 5 Replies View Related

Percentage Query

Aug 5, 2007

Hi Guys,

I am in need some uinderstanding of how to return the percentage of calls that were closed, between 1, 2 ,3, 4 and 5 days (possibly extendable to catch all those that were holding on to jobs to make it look like the were busy) as well as including all open jobs.

Because of my newbie status I am stumped at how to even start a query of this magnitude, and no this is not a school assignment :-) just some one who is very new to T-SQL and wanting to learn how to extract data from an MS SQL database server, as I find it fascinating, also my bosses find it fascinating the type of data I am returning for them so far.

So enough waffling.

This query will be run through VBA, any variable(s) will be supplied by VBA.

DB name = Envisage
Table name = HD_Call
Columns = DateRaised (datetime, null), DateCompleted (datetime, null)


Help in understanding how to construct this query will be grately appreciated.

View 13 Replies View Related

Percentage Measure

Sep 14, 2004

Hi,

easy question for advanced users:

I have a dimension called "Movement rate". The members are "1" to "6".
There is a measure called "No. of Products".

What i easily like to do is showing the percentages "No. of Product" for each "Movement rate" of the amount of "No. of Products" without using the frontend-tool.

Thx in advance for any help.

Andreas

View 2 Replies View Related

How To Calculate Percentage

Apr 28, 2004

b/w two colums?

i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanks

View 8 Replies View Related

Calculate Percentage Value

Aug 29, 2013

I have requirement in which i need to calculate percentage value based on billamount and concession amount..to calculate the percentage part and show the o/p.

SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT],
FROM BILL_MASTER BM
INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N
WHERE BM.BILL_SETTLED_C='Y'

[code]....

View 5 Replies View Related

Calculate Percentage In Sql

Dec 13, 2005

Hi, Please help.

I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.

Please, help.

Thk


SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O

View 3 Replies View Related

Calculate Percentage In Sql

Dec 13, 2005

Hi, Please help.

I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.

Please, help.

Thk


SELECT
CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD,
COUNT(ALERT_RECEIVED_DATE) FRAUDCT,
SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK,
SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION,
SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION
FROM
TBLFRAUDFINDER O

View 1 Replies View Related

Percentage Of Totals

Nov 14, 2006

I am relatively new to SQL i.e. I know the basics!

What I am trying to do is: I have a database of customer spend and I am trying to segment them into certain %ages of the search results. So, for example, find all of the customers that are in the top 10% of spenders (I also want to only select customers with a spend of greater than x!). I am trying to do this using a Case When but feel that I may be out of my depth.

Please help if you can!!

Thanks, Kris

View 4 Replies View Related

Sum And Percentage In Query

Jun 26, 2007

I have following tables structure

Employee
---------
EMP_ID varchar PK
NAME varchar
DEPARTMENT_CODEvarchar
POSITION_CODEint

Position
--------
POSITION_CODEint PK
POSITION_NAMEvarchar

Department
----------
DEPARTMENT_CODEvarchar PK
DEPARTMENT_NAMEvarchar


Training_Module
---------------
TRA_ID varchar PK
TRA_NAMEvarchar
TRA_GROUPvarchar (three group, A,B and C)

View_Training_Module
--------------------
MV_ID int PK
EMP_ID varchar
TRA_ID varchar
VIEW_DATE datetime


Training_Module table data like this
TRA_ID..TRA_NAME..TRA_GROUP
--------------------------------------
v01SafetyVideo1G1
v02SafetyVideo2G1
v03SafetyVideo3G1
V04SafetyVideo4G2
V05SafetyVideo5G2
v06SafetyVideo6G2
v07SafetyVideo7G3
v08SafetyVideo8G3
v09SafetyVideo9G3


View_Training_Module table data like this
EMP_ID.........TRA_ID....VIEW_DATE
-------------------------------------------
p0006367V016/2/2007
p0006367V026/2/2007
p0006367V036/2/2007
p0003892V016/12/2007
p0003892V026/12/2007
p0003892V036/12/2007
p0003890V016/15/2007
p0003890V026/15/2007
p0003890V036/15/2007
p0001232V046/16/2007
p0001232V056/16/2007
p0001232V066/16/2007
p0001230V076/17/2007
p0001230V086/18/2007
p0001230V096/18/2007

We have 44 Safety training videos (15 minutes)

How can calculate the percentage of each employee in query ?

if emploee view 22 video it means that this employee
50% view the videos.


We have to calcuate
1. total number of video view by each employee, sum
2. each employee perentage of viewing.ie. percentage %
3. Group wise percentage, ?
we have three group A,B,and C, calcuate the each
group percentage in query ?


regards
Martin

View 4 Replies View Related

Calculating A Percentage

Mar 25, 2008

My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:

---------------------
Members
---------------------
ID | Goal
---------------------


---------------------
Donors
---------------------
Amount | MemberID
---------------------


I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:

CREATE PROCEDURE sproc_GetGoalPercentage
(
@memberid int
)
AS
SELECT(SUM(D.Amount)/M.Goal)*100
FROMMembers AS M INNER JOIN Donors AS D
ON M.ID = D.MemberID
WHEREM.ID = @memberid
GROUP BY M.Goal
RETURN


All it does is return 0.

View 7 Replies View Related

Query For Percentage Of A SUM

Jul 23, 2005

I am trying to figure out the syntax for a query that will essentiallygive me the Percentage each of my areas contributes to the Whole. Iknow this can be achieved by multiple queries but I would like to keepit intact as one single query if possible.For Example I have the following data set--AREA MOUNE 1234SO 4312WE 12312MW 97123NE 1123SO 31WE 312MW 971The results I would like to see would look likeAREA MOU PERCENTMW 98094 .83536NE 2357 .02007WE 12624 .10751SO 4352 .03706The query I came up with is--SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, sum(MOU) /(SELECT SUM(MOU) AS TOTAL_MOUFROM [2004_NOVEMBER_COST])as[PERCENT]FROM [2004_NOVEMBER_COST]GROUP BY AreaAll seems to calculate with the exception of the Percent where all thepercentages are 0's.I think I need to take the first line AREA_TOTAL and now divide by theSUM(MOU) like this--SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL /(SELECT SUM(MOU) AS TOTAL_MOUbut I get Invalid Column.I essence I think it is a simple query but I am hitting a wall. Anyadvice would help.Thanks,Ben

View 3 Replies View Related

Percentage Column AND Row

Jun 22, 2007

Hello,



I have a matrix that looks as follows:



Name Jan Feb Total

John 5 6 11

Mary 3 4 7

Total 8 10 18



I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:



Name Jan Feb Total %

John 5 6 11 60%

Mary 3 4 7 40%

Total 8 10 18 100%

% 40% 60% 100% 100%



I am so stuck on this I can pull my hair out.



Thanks!



Michael



p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.

View 3 Replies View Related

Percentage Format

Feb 14, 2007

I am trying to display a figure as a Percentage.

The figure is 2.00000. I want it to show as 2.00%.

When i go into the formatting i select P but it displays the figure as 200.00%.

What formatting code should i use to get 2.00%?

View 7 Replies View Related

Percentage Not Being Calculated

Oct 5, 2007

What's incorrect with my sql so that the pct is not being calculated? I'm returning all zeros.
------------------------------------------------------------------------------------------------



SELECT

sa.schoolc sch#,

s.schname AS School,

Y = sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END),

N = sum(CASE WHEN u.logindate IS NULL THEN 1 END),

pct = (sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)/

(sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)+sum(CASE WHEN u.logindate IS NULL THEN 1 END))) * 100


FROM

users AS u

INNER JOIN stugrp_active AS sa ON u.username = sa.suniq

INNER JOIN school AS s ON s.schoolc = sa.schoolc


WHERE

u.username like '3%'

AND sa.schoolc >= 300


GROUP BY

sa.schoolc,

s.schname


ORDER BY

sa.schoolc

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved