Calculating Percentage In A Query

[arohulich]

I have a single table with several fields representing inspection count data. One of the fields has pass / fail as its data. If I generate a Select statement using a UNION function between the items that pass vs fail. The result set lists multiple items as the corresponding fields along with the count of items that pass vs fail. For example the result set currently looks like this:

Item ! Count
----------------
Item1 ! 1 - This Line is the pass count for Item1
Item1 ! 1 - This Line is the fail count for Item1
Item2 ! 2 - This Line is the pass count for Item2
Item2 ! 4 - This Line is the fail count for Item2

What I would like to get as a result set is

Item ! Percent Pass
----------------
Item1 ! 50% - This Line represents % Pass for Item1
Item2 ! 33% - This Line represents % Pass for Item2


The calculation takes the pass count / (pass counts + fail counts)


Is there any easy Query string to do this function? Thanks in advance

Alan

 

[ecniv]

Item ! Count
----------------
Item1 ! 1 - This Line is the pass count for Item1
Item1 ! 1 - This Line is the fail count for Item1
Item2 ! 2 - This Line is the pass count for Item2
Item2 ! 4 - This Line is the fail count for Item2

What I would like to get as a result set is

Item ! Percent Pass
----------------
Item1 ! 50% - This Line represents % Pass for Item1
Item2 ! 33% - This Line represents % Pass for Item2


The calculation takes the pass count / (pass counts + fail counts)

Something like this?

Select [table].[item], ([table].[count]/[subqTotalCounts].[TotalCount]) * 100 as Percentage
From [table] left join
(
Select [table].[Item],sum([table].[count]) as TotalCount 
from [table] 
group by [table].[item]
) as subqTotalCounts on [table].[item]=subqTotalCounts.[item]
Where [table].[status]='Pass'

Notes:
- I'm not sure whether it should be group by and having instead of Where
- You need a status of Pass/Fail or some id that represents this
- I've used Item as the joining unique field, but you probably should use an ItemID field instead
- The above sql was written from my head and should be used only to base your ideas on

Vince

 

[arohulich]

Vince,

First of all, Thanks for the quick reply. It is a single table with several fields
that is being queried. My client wants to create an output table with five
fields as I just found out this morning. It would look something like this:

The input table looks like this:

Org ! Loc ! Item ! Attrib !
-----------------------------
Demo ! Plaza1 ! A ! Pass !
Demo ! Plaza1 ! A ! Soil !
Demo ! Plaza1 ! A ! Stain !

So you see the attrib is either a Pass, or it is a defect

The Output Table Desired looks like this:

Item ! PassCount ! FailCount ! TotalCount ! %Pass
--------------------------------------------------
A ! 1 ! 2 ! 3 ! 33.3
B ! 8 ! 31 ! 39 ! 20.5

Where %Pass is the PassCount/TotalCount

I am not sure a Query can accomplish this. In addition I am limited as to the SQL that is available as I am using SQL CE (Subset of Access), but most Access Queries work. I attempted to use the SQL you suggested and am receiving an error saying that the Join is not supported. I am continuing to look at the syntax. I tested your query in Access 2000. Once I get it working
there I can convert it to work on SQL CE.

Thanks in advance, Alan

 

[arohulich]

Vince,

More info - I re-tried your query and it works if I add a column called status to the table. I will look at extending your thoughts to include my new requirements as stated in my last reply. Thanks for the "off the top of your head" reply it was GREAT! I wish my "off the top my head" thoughts were as good and concise.

Regards,
Alan

 

[EMP]

I would like to offer an alternative.

SELECT [TableName].[Item], -Sum([TableName].[Attrib]="Pass") AS PassCount,
-Sum([TableName].[Attrib]="Soil" Or [TableName].[Attrib]="Stain") AS FailCount,
Count(*) AS TotalCount, Format((PassCount/TotalCount),"Percent") AS PercentPass
FROM [TableName]
GROUP BY [TableName].[Item];


It works in Access 2000. Hope it works in SQL CE, too.

 

[ecniv]

The status field in my query is the attrib field you posted...

As to the calculations you could in theory get away with something like the following... Might need to be looked at further.

Select [table].[item],[table].[count] as [Pass],[subqTotalCounts].[TotalCount]-[table].[count] as [Fail], ([table].[count]/[subqTotalCounts].[TotalCount]) * 100 as Percentage
From [table] left join
(
Select [table].[Item],sum([table].[count]) as TotalCount 
from [table] 
group by [table].[item]
) as subqTotalCounts on [table].[item]=subqTotalCounts.[item]
Where [table].[attrib]='Pass'

Sorry, I cannot remember if Count was already totalled or if you need to do this too as a sub query in this one.

Good luck in completing it.

Vince

 

[arohulich]

Thanks everyone for your help! It looks like that last append will work, just need to figure out an alternative to the Format command. It is not supported in SQL CE, everything else is.

Regards,
Alan

 

[arohulich]

As it turns out the -Sum([TableName].[Attrib]="Pass") function is not valid in SQL CE. Back to the drawing board. That was sure a clean solution if it worked. Thanks for trying...

Regards,
Alan

 

[ecniv]

...out the -Sum([TableName].[Attrib]="Pass") function...

Eh?
Whats that??
You sum the field, and filter it in the having clause...
Or the Where clause (as I said you'd need to look at that)

Whats the Sql statement you have at the moment?


Vince

 

[arohulich]

Vince,

As it turns out the left join is not supported in SQL CE so I am trying to modify your SQL such that it does not require the left join. EMP, provided a neat solution except that the -Sum([TableName].[Attrib]="Pass") portions of the query also is not supported in SQL CE. Currently I am using two Selects with a UNION. See below:

SELECT Area AS Area1, Count(Area) AS Count1, 'Pass' AS Status
FROM InputData
WHERE Building = 'Plaza 1' AND Organization = 'Demo' AND Location = 'Loc1' AND Attrib = 'Pass'
GROUP BY Area
UNION
SELECT Area AS Area2, Count(Area) AS Count2, 'Fail' AS Status1
FROM InputData
WHERE Building = 'Plaza 1' AND Organization = 'Demo' AND Location = 'Loc1'
AND Attrib <> 'Pass'
GROUP BY Area ;

This yields the following:

Area1 ! Count1 ! Status
Conf ! 1 ! Pass
Conf ! 2 ! Fail
Rest ! 2 ! Pass
Rest ! 5 ! Fail

What I really want is:

Area1 ! PassCnt ! FailCnt ! TotalCnt ! PercentPass
Conf ! 1 ! 2 ! 3 ! 33.3
Rest ! 2 ! 5 ! 7 ! 28.6


Any help would be greatly appreciated.

Regards,
Alan

 

[EMP]

I have attached an A2K database. It contains another version of my previous query.

Query2:-
SELECT [TableName].[Item], Abs(Sum([TableName].[Attrib]="Pass")) AS PassCount,
Abs(Sum([TableName].[Attrib]="Soil" Or [TableName].[Attrib]="Stain")) AS FailCount,
Count(*) AS TotalCount, (PassCount/TotalCount)*100 AS PercentPass
FROM TableName
GROUP BY [TableName].[Item];


When it is run in the database, it returns:-

Item	PassCount	FailCount	TotalCount	PercentPass
A		1		2		 3	33.3333333333333
B		8	       31		39	20.5128205128205

However, if Abs() is also not supported in SQL CE, you can try changing Abs to -1* as in my Query3 in the database.

SELECT [TableName].[Item], -1*(Sum([TableName].[Attrib]="Pass")) AS PassCount,
-1*(Sum([TableName].[Attrib]="Soil" Or [TableName].[Attrib]="Stain")) AS FailCount,
Count(*) AS TotalCount, (PassCount/TotalCount)*100 AS PercentPass
FROM TableName
GROUP BY [TableName].[Item];


Good luck!

 

[arohulich]

Vince,

I really appreciate you hanging in there and trying to help me. Your queries work great on A2K, but the problem is that the SQL CE doesn't support the expressions within the SUM function, ie - Sum([TableName].[Attrib]="Pass").
I am beginning to wonder if a Simple query is going to work for my application?

I am currently pursuing a Union query, but that doesn't give me the desired
result, ie I get two rows for every item that has 'Pass' or 'Fail' criteria.
The data collected is tagged with a 'Pass' or a deficiency attribute (Fairly long list of def attributes) as the 'Fail items (Everything that is not a 'Pass' item). In addition these records are included in a larger database and have to be selected out by Organization/ Location/ Building, etc before I even look at the 'Pass' criteria. Therefore I need a WHERE clause to get to the desired records so a Union query would work for the pass vs fail items. The problem is how do I take the result of the Union query and feed it into another query to get the desired results as shown in my previous post. I think pursuing this approach is my best bet. I am a rank amateur at best with SQL (I can handle simple queries, but multiple level queries are still a challenge). Thanks for any help you can give me.

Alan

 

[EMP]

To use a UNION query, you can do it like this:-

Query1:
SELECT Area AS Area1, Count(Area) AS Pass, Null as Fail
FROM InputData
WHERE Building = 'Plaza 1' AND Organization = 'Demo' AND Location = 'Loc1' AND Attrib = 'Pass'
GROUP BY Area
UNION
SELECT Area AS Area2, Null, Count(Area)
FROM InputData
WHERE Building = 'Plaza 1' AND Organization = 'Demo' AND Location = 'Loc1'
AND Attrib <> 'Pass'
GROUP BY Area;

Query2:
SELECT Query1.Area1, Max(Query1.Pass) AS PassCnt, Max(Query1.Fail) AS FailCnt,
PassCnt+FailCnt AS TotalCnt, PassCnt/TotalCnt*100 AS PercentPass
FROM Query1
GROUP BY Query1.Area1;


Another Query2:
SELECT Query1.Area1, Max(Query1.Pass) AS PassCnt, Max(Query1.Fail) AS FailCnt,
Max([Query1].[Pass])+Max([Query1].[Fail]) AS TotalCnt,
Max([Query1].[Pass])/(Max([Query1].[Pass])+Max([Query1].[Fail]))*100 AS PercentPass
FROM Query1
GROUP BY Query1.Area1;


Run Query2.