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

 

[bborissov]

Sum([Count]) * 100.00 AS Percentage

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Unfortunately this gives me all the total figures times by 100, I want to see them as a percentage, some results as below , any ideas please

Total percentage
12 1200.00
5294 529400.00
9 900.00

 

[johnherman]

If the figures are times 100, why not just divide by 100?

(Sum([Count]) * 100.00)/100.00 AS Percentage

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[bborissov]

Please provide some example data and desired result.



Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Ok using the code I am getting this result out.

This is from the result

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]
,(Sum([Count]) * 100.00)/100.00 AS Percentage


Thanks

 

[SkipVought]

First thought: percentage with respect to WHAT?

What do all these numbers mean?

What is the sum of Makeup: 12? Is that supposed to be 12%? 12% of WHAT?

It's very suspect, when the next row contains 5294 OTIF. Seems like a different category. 5294% of WHAT?

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djj55]

Hello, what exactly are you trying to show the percent of?
Most times percent is (somevalue/total) * 100



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

For computing a percentage you need to define what 100% is. If the sum of all totals defines a total total to which the group totals are partial totals you can't compute that percentages in the same query, as you'll first need the overall sum as reference 100% value. So either you get that out of a subquery or you compute percentages as aftermath, ie create a numeric empty field and compute the percentage after knowing all data.

Simple math lesson: The sum of all percentages of partial groups must be 100. count * 100/100 is far off. it's simply converting int to numeric with all 0 decimal places. Why? Well 100/100 = 1.0000, surprise.

Bye, Olaf.

 

[Cpreston]

Hi

Ok the numbers are a count of salesrep names with OTIF reasons against their name.
So for example Bob had 3 counts for the OTIF Make-up.

The total 12 is the total for the OTIF Make-up. So the % probably should be the % against the grandtotal of the Total column
The OTIF is a measure of delivery performance so the high total is correct 5294

Thanks

 

[Cpreston]

Hi

yes you are all correct, the % should the sum of all total rows, just cant figure out how to put it into syntax.

I tried rolloever but could not get that to work

Thanks

 

[gmmastros]

Just to clarify....

The percentage for the first row should be...

12 / (12 + 5294 + 9 + 9 + 65 + 17 + 29 + 1) * 100 = 0.22 %

Is this correct?

-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

 

[Cpreston]

Correct

 

[gmmastros]

The tricky part here is... you cannot perform an aggregate on an aggregate without jumping through a couple of hoops. Can you show the complete code?

-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

 

[Cpreston]

; With Data As
(
SELECT count(oh.udfSalesOrderOTIF) AS Count
, oh.OrderStatus
, sr.Name
, oh.udfSalesOrderOTIF
FROM dbrderHeader 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))

)
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]
--,(Sum([count].total) * 100.00)/100.00 AS Percentage


From Data
Group By udfSalesOrderOTIF

 

[SkipVought]

I don't know what your numbers mean. You surely do. So why "probably"?

The answer should be "Yes" or "No!"

From my perspective in my sunroom sipping my coffee, I'd guess No!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

OTIF means On Time in Full for delivery's
IF it is recorded as OTIF then that is good
If for whatever reason the delivery is not OTIF then we make this against the order the reason why it is not OTIF

The count is therefore on reasons. the % would tell us the % in the areas we are failing on all deliveries.

 

[gmmastros]

It's hard to tell if this is going to work for you because it's difficult to test. Give it a try.

; 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].total) / GrandTotal.GrandTotal AS Percentage 
From   Data 
       Cross Join GrandTotal
Group By udfSalesOrderOTIF

-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

 

[Cpreston]

Hi

Ok tried it and I am getting

Msg 4104, Level 16, State 1, Line 34
The multi-part identifier "count.total" could not be bound.


The line 34 is as below

, Sum([Count]) As [Total]
, 100.0 * Sum([count].total) / GrandTotal.GrandTotal AS Percentage (this is line 34)

 

[gmmastros]

; 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

-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