Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

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.

                     
0
c_v_wright
Asked:
c_v_wright
  • 2
  • 2
  • 2
1 Solution
 
David KrollCommented:

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

0
 
c_v_wrightAuthor Commented:
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)
0
 
David KrollCommented:
You have "from MyLearningMap = t.associate" and it should be "from MyLearningMap where associate = t.associate"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Kevin CrossChief Technology OfficerCommented:
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:
http://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

0
 
c_v_wrightAuthor Commented:
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>


<p>&nbsp;</p>
<p>&nbsp;</p>
<table width="100%" border="1">
  <thead>
    <tr>
      <td><b>associate</b></td>
      <td><b>pctcomplete</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" startspan
    s-columnnames="associate,pctcomplete" s-columntypes="202,5"
    s-dataconnection="LearningMap" b-tableformat="TRUE" b-menuformat="FALSE"
    s-menuchoice="associate" s-menuvalue="associate" b-tableborder="TRUE"
    b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
    b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE" s-recordsource
    s-displaycolumns="associate,pctcomplete" s-criteria s-order
    s-sql=" select distinct associate,&lt;br&gt;(((select count(*) from MyLearningMap where associate = t.associate and Mystatus = 'Completed')/(select count(*) from MyLearningMap where associate = t.associate)) * 100) + '%' as pctcomplete&lt;br&gt;from MyLearningMap t "
    b-procedure="FALSE" clientside SuggestedExt="asp" s-DefaultFields
    s-NoRecordsFound="No records returned." i-MaxRecords="256" i-GroupSize="0"
    BOTID="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc"
    u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
    local_preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;Database Results regions will not preview unless this page is fetched from a Web server with a web browser. The following table row will repeat once for every record returned by the query.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;"
    preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;"
    b-WasTableFormat="TRUE" --><!--#include file="_fpclass/fpdblib.inc"-->
<%
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
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-CheckSum="32360" -->
    <tr>
      <td><!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="associate,pctcomplete" s-column="associate"
        b-tableformat="TRUE" b-hasHTML="FALSE" clientside
        local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;associate&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;associate&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"associate")%><!--webbot
        bot="DatabaseResultColumn" endspan i-CheckSum="22085" -->
      </td>
      <td><!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="associate,pctcomplete" s-column="pctcomplete"
        b-tableformat="TRUE" b-hasHTML="FALSE" clientside
        local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;pctcomplete&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;pctcomplete&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"pctcomplete")%><!--webbot
        bot="DatabaseResultColumn" endspan i-CheckSum="32792" -->
      </td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE"
    b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0"
    clientside tag="TBODY"
    local_preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;"
    preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-CheckSum="62730" -->
  </tbody>
</table>


</body>
</html>


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

Thanks for your help!
0
 
Kevin CrossChief Technology OfficerCommented:
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.  
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now