-1

I am trying to calculate how many packs of sweats a kid needs based on the amount of individual sweets required. I am trying to figure out the SQL syntax to do this.

I have used ceiling with divide but still not working

select CEILING(NoOfSweets / SweetsPerPack) AS NoOfPacks

Scenarios:

NoOfSweets   SweetsPerPack   RequiredOutCome   NoOfPacks
--------------------------------------------------------
10               10             1                 1
 5               10             1                 0
20               10             2                 2
 8               10             1                 0      
 7                5             2                 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stanton Roux
  • 141
  • 1
  • 14

1 Answers1

2

If the values are integers, then SQL Server does integer division. So, 1/2 is 0 rather than 0.5. I find that the simplest way to get a number with decimal points is to multiply by 1.0:

CEILING(NoOfSweets * 1.0 / SweetsPerPack)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786