Percent Calculation 1

[Cpreston]

Hi

I have some code with a sum at the end which works fine, I would like to add another calculation that shows the % of the totals

, Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group]
, Sum([Count]) As [Total]

I have tried

, Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group]
, [Total] * 100 as percentage

But it does not like the *. I have tried various ways but cannot get it to work, could someone advise the syntax on this please

Thanks

 

[Cpreston]

Hi

yes that appears to have cracked it.

many thanks for that it as been a massive help

Thanks

 

[gmmastros]

Here's the explanation.

The challenge here is that we need to get the grand total so that we can use it in the percentage calculation.

; With Data As
(
  SELECT count(oh.udfSalesOrderOTIF) AS Count
         , oh.OrderStatus
         , sr.Name
         , oh.udfSalesOrderOTIF
  FROM   dbo.OrderHeader oh
         INNER JOIN dbo.SalesRep sr
           ON oh.SalesRepID = sr.SalesRepID
  GROUP BY oh.OrderStatus
         , sr.Name
         , oh.udfSalesOrderOTIF
  HAVING (oh.OrderStatus = 8)
         AND (NOT (COUNT(oh.udfSalesOrderOTIF) IS NULL))
         AND (NOT (oh.udfSalesOrderOTIF IS NULL))
)[!], GrandTotal As
(
  Select Sum([Count]) As GrandTotal
  From   Data
)[/!]
Select udfSalesOrderOTIF
       , Coalesce(Min(Case When Name = 'Bob Banks' Then [Count] End), 0) As [Bob]
       , Coalesce(Min(Case When Name = 'Antony Considine' Then [Count] End), 0) As [Antony]
       , Coalesce(Min(Case When Name = 'Eddie O''Riordan' Then [Count] End), 0) As [Eddie]
       , Coalesce(Min(Case When Name = 'Lawrie Gingel' Then [Count] End), 0) As [Lawrie]
       , Coalesce(Min(Case When Name = 'Office' Then [Count] End), 0) As [Office]
       , Coalesce(Min(Case When Name = 'Scott Laughton' Then [Count] End), 0) As [Scott]
       , Coalesce(Min(Case When Name = 'Ray Cowburn' Then [Count] End), 0) As [Ray]
       , Coalesce(Min(Case When Name = 'Lee Heitzman' Then [Count] End), 0) As [Lee]
       , Coalesce(Min(Case When Name = 'Administration' Then [Count] End), 0) As [Admin]
       , Coalesce(Min(Case When Name = 'Paul Lowery' Then [Count] End), 0) As [Paul]
       , Coalesce(Min(Case When Name = 'Group' Then [Count] End), 0) As [Group]
       , Sum([Count]) As [Total]
       , 100.0 * Sum([count]) / GrandTotal.GrandTotal AS Percentage 
From   Data 
       Cross Join GrandTotal
Group By udfSalesOrderOTIF

The part is red is considered a common table expression (CTE). It's the same structure as the "Data" part of the query. The cool part about CTE's is that you can treat the containing query as though it were it's own table.

In this case, the containing query is simply getting the grand total. In fact, this query is really a "table" with just one row and one column. We need to use this value in each row of our output, which explains the cross join part at the end.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom