Hello, I'm currently at a loss trying to calculate percentages based on two fields within my script.
SELECT[AT].[Site_Name] [Site Name]
,@Month01 [Month]
,ISNULL (COUNT ([AT].[Event_Code]), 0) [Total Picks]
,ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Pick', 'pick during maintenance') THEN 1 END), 0) [Succesful Picks]
,ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Mispick', 'On-charge mispick', 'Mispick during maintenance', 'On-charge mispick during maintenance')
THEN 1 END), 0) [Mispicks]
,ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1 END), 0) [Zero Availability Picks]
,'' [Percentage - Succesful Picks]
,'' [Percentage - Mispicks]
,'' [Percentage - Zero Availability Picks]
I can't get to a formula to work for dividing the [Succesful Picks] against the [Total Picks] columns. I assume once that is resolved, replicating for the other two fields would be a case of copy, paste and amend to fit.
If anyone can either point me in the right direction or give me a solution I would be greatly appreciative.
Thanks, Richard...
March 23, 2023 at 12:21 pm
Logic should be something like this
((Successful Pick / Total Picks) * 100)
((Mispick / Total Picks) * 100)
((Zero Pick / Total Picks) * 100)
Does this work for you?
SELECT[AT].[Site_Name] [Site Name]
,@Month01 [Month]
,ISNULL (COUNT ([AT].[Event_Code]), 0) [Total Picks]
,ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Pick', 'pick during maintenance') THEN 1 END), 0) [Succesful Picks]
,ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Mispick', 'On-charge mispick', 'Mispick during maintenance', 'On-charge mispick during maintenance')
THEN 1 END), 0) [Mispicks]
,ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1 END), 0) [Zero Availability Picks]
,((ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Pick', 'pick during maintenance') THEN 1 END), 0) / ISNULL (COUNT ([AT].[Event_Code]), 0)) * 100) [Percentage - Succesful Picks]
,((ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Mispick', 'On-charge mispick', 'Mispick during maintenance', 'On-charge mispick during maintenance')
THEN 1 END), 0) / ISNULL (COUNT ([AT].[Event_Code]), 0) ) * 100) [Percentage - Mispicks]
,((ISNULL (SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1 END), 0) / ISNULL (COUNT ([AT].[Event_Code]), 0)) * 100) [Percentage - Zero Availability Picks]
March 23, 2023 at 12:31 pm
Thanks Ant-Green, just gave that a try and each of them comes up with a 0 value. Here is some example data for a row where I've given the values I have but amended the name for the site.
Site Name = Test
@Month01 = 2023-02
Total Picks = 3733
Successful Picks = 3156
Mispicks = 576
Zero Availability Picks = 1
So the [Event_Code] are text values hence the need to use a count for the [Total Picks] and then the CASE is then to pick out the event types that have happened. I've tried replacing the SUM to all COUNT gives me the same results and still as 0's in the percentage fields.
Thanks, Richard...
March 23, 2023 at 1:01 pm
Instead of dividing by 100 try dividing by 100.0. With division the implicit type conversion is to INT unless the denominator is non-INT, either NUMERIC or FLOAT. Also, imo the ISNULL's aren't necessary as your CASE expressions should include ELSE 0's. COUNT never returns NULL. SUM ignores NULLS when used with GROUP BY. Also moving the CASE expressions to a CROSS APPLY might d.r.y. the code out a little
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
You're doing integer division, which is why you're getting 0's. Instead of specifying 1 (integer), specify 1.0 (decimal).
Also, you don't need the ISNULL() if you specify the CASE expression correctly.
,SUM (CASE WHEN [AT].[Event_Code] IN ('Pick', 'pick during maintenance') THEN 1.0 ELSE 0 END) [Succesful Picks]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2023 at 1:04 pm
It may be an integer-division issue.
Try multiplying by 100.0 first, to force a decimal calculation:
Successful Picks * 100.0 / Total Picks
Note also that you have misspelled 'successful'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 23, 2023 at 1:15 pm
Thanks all.
Yeah I noticed the misspelling after I had posted the example above, all corrected now 🙂
SELECT[AT].[Site_Name] [Site Name]
,@Month01 [Month]
,COUNT ([AT].[Event_Code]) [Total Picks]
,COUNT (CASE WHEN [AT].[Event_Code] IN ('Pick', 'pick during maintenance') THEN 1 END) [Succesful Picks]
,COUNT (CASE WHEN [AT].[Event_Code] IN ('Mispick', 'On-charge mispick', 'Mispick during maintenance', 'On-charge mispick during maintenance') THEN 1 END) [Mispicks]
,COUNT (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1 END) [Zero Availability Picks]
,SUM (CASE WHEN [AT].[Event_Code] IN ('Pick', 'pick during maintenance') THEN 1.0 END) / COUNT ([AT].[Event_Code])* 100.0 [Percentage - Succesful Picks]
,SUM (CASE WHEN [AT].[Event_Code] IN ('Mispick', 'On-charge mispick', 'Mispick during maintenance', 'On-charge mispick during maintenance') THEN 1.0 END) / COUNT ([AT].[Event_Code]) * 100.0 [Percentage - Mispicks]
,SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 END) / COUNT ([AT].[Event_Code]) * 100.0 [Percentage - Zero Availability Picks]
That's it above and happy to say it now works 🙂 Thank you so much, I'm all self-taught on SQL so there are times when I struggle but am ever so grateful for forums such as this.
Thanks, Richard...
March 23, 2023 at 2:30 pm
Instead of dividing by 100 try dividing by 100.0. With division the implicit type conversion is to INT unless the denominator is non-INT, either NUMERIC or FLOAT. Also, imo the ISNULL's aren't necessary as your CASE expressions should include ELSE 0's. COUNT never returns NULL. SUM ignores NULLS when used with GROUP BY. Also moving the CASE expressions to a CROSS APPLY might d.r.y. the code out a little
That's not correct. It's doing integer division, because BOTH the numerator and denominator are integer expressions. It will do decimal division if EITHER (not just the denominator) is non-INT. There will NEVER be an implicit conversion to a data type with a lower precedence within an expression, only when assigning or setting the value of a variable or field with a lower precedence.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2023 at 3:06 pm
It turns out, upon further review, a non-INT as either the numerator OR the denominator will switch the implicit conversion to NUMERIC.
select sql_variant_property(1/4, 'basetype') int_int,
sql_variant_property(1.0/4, 'basetype') num_int,
sql_variant_property(1/4.0, 'basetype') int_num;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 23, 2023 at 3:43 pm
I'm having to add back in the ISNULL on the percentage calculations so that NULL fields are not displayed. SO example of an updated string is this.
ISNULL (FORMAT ((SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 END) /COUNT ([AT].[Event_Code]) * 1.0), 'P2'), '0.00%') [Percentage - Zero Availability Picks]Is there a 'cleaner' way of doing this? Thanks, Richard...
March 23, 2023 at 4:18 pm
I'm having to add back in the ISNULL on the percentage calculations so that NULL fields are not displayed. SO example of an updated string is this.
ISNULL (FORMAT ((SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 END) /COUNT ([AT].[Event_Code]) * 1.0), 'P2'), '0.00%') [Percentage - Zero Availability Picks]Is there a 'cleaner' way of doing this? Thanks, Richard...
YES.
FORMAT ((SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 ELSE 0.0 END) /COUNT ([AT].[Event_Code]) * 1.0), 'P2'), '0.00%') [Percentage - Zero Availability Picks]Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 23, 2023 at 4:26 pm
PS_Richard wrote:I'm having to add back in the ISNULL on the percentage calculations so that NULL fields are not displayed. SO example of an updated string is this.
ISNULL (FORMAT ((SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 END) /COUNT ([AT].[Event_Code]) * 1.0), 'P2'), '0.00%') [Percentage - Zero Availability Picks]Is there a 'cleaner' way of doing this? Thanks, Richard...
YES.
FORMAT ((SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 ELSE 0.0 END) /COUNT ([AT].[Event_Code]) * 1.0), 'P2'), '0.00%') [Percentage - Zero Availability Picks]
Had to amend slightly to this.
FORMAT ((SUM (CASE WHEN [AT].[Event_Code] IN ('Zero Available Pick') THEN 1.0 ELSE 0.0 END) /COUNT ([AT].[Event_Code]) * 1.0), 'P2') [Percentage - Zero Availability Picks]As didn't need to force the 0.00% in. Thanks though for showing me a different way to tackle this.
Thanks, Richard...
March 23, 2023 at 4:35 pm
The FORMAT() function performs notoriously poorly. If you are processing a lot of data, I suggest you remove it and find another way (either in code, or using Excel or whatever other tool you are presenting the data with).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply