asked on

MS SQL query record count converted into percent based on records completed

Hello Experts,

Using MS SQL Server 2005 I'm trying to get the total Course count for an associate and based on that number query the percent completed by name

MyTrainingID Associate Status Course
1 James Smith Completed Travel Policy
2 Susan Connolly Not Completed Orientation
3 Greg Spelling Completed Orientation
4 James Smith Not Completed Orientation

To look like this:

Associate Percent Complete
James Smith 50%
Susan Connolly 0%
Greg Spelling 100%

Thanks in advance for your help; I'm new to SQL.

David Kroll

select distinct associate,
(((select count(*) from table where associate = t.associate and status = 'Completed')/(select count(*) from table where associate = t.associate)) * 100) + '%' as pctcomplete
from table t

Open in new window

c_v_wright

ASKER

Thanks for the quick reply.

When I use the code I get this error:
Server error: Unable to retrieve schema information from the query:

select distinct associate,

(((select count(*) from MyLearningMap = t.associate and MyStatus = 'Completed')/(select count(*) from MyLearningMap = t.associate)) * 100) + '%' as pctcomplete

from MyLearningMap t

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/LearningMap7.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression '(((select count(*) from MyLearningMap = t.associate and MyStatus = 'Completed')/(select count(*) from MyLearningMap = t.associate)) * 100) + '%''.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

ASKER CERTIFIED SOLUTION

David Kroll

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

Hi.

Firstly, I think the previous suggestion should work, but caution you on division of integers in T-SQL. However, the error you are getting suggests that you have a linked server to an Access database involved. With that said, my suggestion would be to use conditional aggregates to solve this:
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_3527-A-SQL-Tidbit-Conditional-Aggregates.html

select [Associate]
     , convert(decimal(12, 2), count(case [Status] when 'Completed' then MyTrainingID end) * 100.0 / count(MyTrainingID)) as [Percent Complete]
from MyLearningMap
group by [Associate]
;

Open in new window

c_v_wright

ASKER

I'm sure it is something on my end, but I'm still having errors.

Here is the code and web address

<html>
<head>
<title>This Web site coming soon</title>

<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
</head>

<body>

 
 
<table width="100%" border="1">
<thead>
<tr>
<td>associate</td>
<td>pctcomplete</td>
</tr>
</thead>
<tbody>

<%
fp_sQry=" select distinct associate, (((select count(*) from MyLearningMap where associate = t.associate and Mystatus = 'Completed')/(select count(*) from MyLearningMap where associate = t.associate)) * 100) + '%' as pctcomplete from MyLearningMap t "
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="LearningMap"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="associate"
fp_sMenuValue="associate"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>


<tr>
<td><%=FP_FieldVal(fp_rs,"associate")%>
</td>
<td><%=FP_FieldVal(fp_rs,"pctcomplete")%>
</td>
</tr>


</tbody>
</table>

</body>
</html>

Web address:
http://www.seetheresults.com/Blank_copy(3).html
web address

Thanks for your help!

Kevin Cross

If this is not truly answered, then I would suggest using 'Request Attention' link and asking a Moderator to re-open this question. During that process, please clarify whether or not you are indeed using MS SQL Server. If you are using MS Access as the datasource to the ASP page shown, then please ask Moderator's to change zone for you.

Additionally what are the errors you are getting. Are they relating to the database or the ASP code?
If the latter, i.e., this question is solved but you have another issue in code, then I would suggest using the 'ask a related question' link right above the comment box and ensure to add Classic ASP programming zone to the question.

Note that you probably want to remove ambiguity from the columns in the subquery regardless of platform.
(select count(*) from MyLearningMap lkp where lkp.associate = t.associate and lkp.Mystatus = 'Completed')/(select count(*) from MyLearningMap lkp where lkp.associate = t.associate)

Check using my version not requiring two subqueries and post back the error messages.

If this is SQL Server, then take note to my earlier comment about division by integers. If you don't account for T-SQL's handling of integer division, you will likely get 0 for results and that may be the error of which you speak.

Anyway, please provide feedback and take appropriate actions and we will help.