how to calculate percentage in sql


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: how to calculate percentage in sql

  1. #1
    Join Date
    May 2006
    Posts
    1

    how to calculate percentage in sql

    Here is the code ,instead of sum(t.regularhours) i want percentage of t.regularhours please help me out i kept this formula for total hours --
    t.regularhours/(select sum(regularhours) from time t)*100 but i got the error as this t.regularhours is not contained in either aggregate or group by function.but when i keep group by w.name,res.employeetype,t.regularhours
    i get all the workgroupnames without getting grouped

    SELECT
    ISNULL(w.Name, 'N/A') AS WorkGroupName,
    'Project' as TimeType,

    case when res.employeetype ='NT' then 'NTNI'
    when res.employeetype= 'EM' then 'Employee'
    when res.employeetype= 'CO' then 'Contractor'
    when res.employeetype= 'MC' then 'Management Consultant'
    when res.employeetype= 'OS' then 'Offshore'
    end Employeetype,
    Sum(t.RegularHours) AS RegularHours
    FROM
    Time t (NOLOCK) LEFT OUTER JOIN
    Workgroupmember wgm (NOLOCK) ON t.ResourceId = wgm.ResourceId and t.WorkgroupId=wgm.WorkgroupId and t.GlobalWorkgroupId=wgm.GlobalWorkgroupId LEFT OUTER JOIN
    Workgroup w (NOLOCK) ON w.WorkgroupId = wgm.WorkgroupId and w.deleted = 0 LEFT OUTER JOIN
    GlobalWorkgroup gwg (NOLOCK) ON wgm.GlobalWorkgroupId = gwg.GlobalWorkgroupId LEFT OUTER JOIN
    Resources res (NOLOCK) ON wgm.ResourceId = res.ResourceId
    where timedate >'12/10/2005' and approvalstatus='A' and w.obs like '00001.00004.00001%'
    group by w.name,res.employeetype

  2. #2
    Join Date
    May 2006
    Posts
    18
    Try this:
    1. Include another column in your SQL which is t.RegularHours.
    2. ...then wrap that sql in a SQL statement that actually calculates the percentage. (make it a subquery).
    jedicoder
    Software for eCommerce and Innovation
    Affordable eCommerce Web Designer
    Website Designer Search Directory

Similar Threads

  1. SQL Percentage of a column
    By sremiger in forum Database
    Replies: 2
    Last Post: 01-05-2006, 12:17 PM
  2. PERCENTAGE AND CONDITIONS sql
    By sereleg in forum VB Classic
    Replies: 0
    Last Post: 03-13-2005, 09:48 PM
  3. Help with looping a dynamic sql string
    By jeff in forum ASP.NET
    Replies: 1
    Last Post: 06-07-2002, 12:12 PM
  4. Can Sql Server do this?
    By DavidR in forum Database
    Replies: 5
    Last Post: 02-15-2001, 12:46 AM
  5. Blocking problem with DSN-less connection
    By Adam Dawes in forum VB Classic
    Replies: 3
    Last Post: 12-21-2000, 12:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center