CODEWITHSUNDEEP
sql-serverpivot-table
Nick
Nick

Reputation: 372

SQL Percentage calculation

Is it possible in SQL to calculate the percentage of the 'StaffEntered' column's "Yes" values (case when calculated column) out of the grand total number of orders by that user (RequestedBy)? I'm basically doing this function now myself in Excel with a Pivot table, but thought it may be easier to build it into the query. Here is the existing sample SQL code:

    Select 
    Distinct

    RequestedBy = HStaff.Name,
    AccountID = isnull(pv.AccountID, ''),
    StaffEntered = Case When DictionaryItem2.Name like '%PLB%' Then 'Yes' Else 'No' end


FROM
 [dbo].[HOrd] HOrd WITH ( NOLOCK )
 left outer join HStaff HStaff with (nolock)
  on HOrd.Requestedby_oid = HStaff.ObjectID
  and HStaff.Active = 1
 left outer join DictionaryItem DictionaryItem2 WITH (NOLOCK)
  ON HSUser1.PreferenceGroup_oid = DictionaryItem2.ObjectID
  AND DictionaryItem2.ItemType_oid = 98

Here is what I am doing in Excel currently with the query results, I have a pivot table and I am dividing the "Yes" values of the "StaffEntered" field out of the Grand Total number of entries for that specific "RequestedBy" user. Essentially Excel is doing the summarization and then I am doing a simple division calculation to obtain the percentage.

enter image description here

Thanks in advance!

Upvotes: 0

Views: 590

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6771

Giving the FROM part of your SQL Statement would allow us to create a more correct answer. This statement will get the totals of yes/no per HStaff name and add it to each detail record in your SQL statement:

WITH    cte
          AS ( SELECT   HStaff.Name ,
                        SUM(CASE WHEN dictionaryItem2.Name LIKE '%PLB%' THEN 1
                                 ELSE 0
                            END) AS YesCount ,
                        SUM(CASE WHEN dictionaryItem2.Name NOT LIKE '%PLB%'
                                 THEN 1
                                 ELSE 0
                            END) AS NotCount
               FROM     YourTable
               GROUP BY HStaff.Name
             )
    SELECT  HStaff.Name AS requestedBy ,
            ISNULL(pv.AccountID, '') AS AccountID ,
            CASE WHEN DictionaryItem2.Name LIKE '%PLB%' THEN 'Yes'
                 ELSE 'No'
            END AS StaffEntered ,
            cte.YesCount / ( cte.YesCount + cte.NotCount ) AS PLB_Percentage
    FROM    yourtable
            INNER JOIN cte ON yourtable.Hstaff.Name = cte.NAME

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

You didn't provide a lot in the way of details but I think this should be pretty close to what you are looking for.

select HStaff.Name as RequestedBy 
    , isnull(pv.AccountID, '') as AccountID 
    , Case When DictionaryItem2.Name like '%PLB%' Then 'Yes' Else 'No' end as StaffEntered 
    , sum(Case When DictionaryItem2.Name like '%PLB%' Then 1 Else 0 end) / GrandTotal
From SomeTable
group by HStaff.Name 
    , isnull(pv.AccountID, '')
    , GrandTotal

Upvotes: 1

Related Questions