asked
Odd Query Behaviour, unable to divide? - Other operations are ok
Hello, I have this code: SELECT dbo.Input_Hardware_Tickets.CostElement, dbo.Input_Hardware_Tickets.Frequency, dbo.HW_Ticket_Count.Total, dbo.Input_Hardware_Tickets.Frequency / dbo.HW_Ticket_Count.Total AS Divide, dbo.Input_Hardware_Tickets.Frequency * dbo.HW_Ticket_Count.Total AS Ex_Times, dbo.Input_Hardware_Tickets.Frequency + dbo.HW_Ticket_Count.Total AS Ex_Plus, dbo.Input_Hardware_Tickets.Frequency - dbo.HW_Ticket_Count.Total AS Ex_Minus `FROM dbo.Input_Hardware_Tickets CROSS JOIN dbo.HW_Ticket_Count` I put in the plus, minus and * just to see if they would work... they work just fine. I'm receiving actual values for them... however for the divide expression - I receive 0's!!! I've been ripping my hair out trying to figure this out... No luck Can anyone suggest anything? - I'm quite new to SQL Server 2008 too My first row returns this: Frequency: 968 Total: 6052 Divide: 0 Ex_Times: 5858336 Ex_Plus: 7020 Ex_Minus: -5084 Why on earth is divide 0? Thanks
Frequency and Total are integers, I would gather. Check this out: select 12 / 100; select 12 / 100.0; select 12.0 / 100; select CAST(12 as decimal(5,0)) / 100; The first case is doing integer division, and in integer terms, 12 / 100 is 0. The next two have a non-integer, so floating point division is used, which gives you a value of 0.12. Finally, we still have 2 integers, but I am explicitly casting one of them as a decimal (up to 5 digits long, with 0 digits after the decimal place). Because of this conversion, we get non-integer division, and a value of 0.12. So for your query, you could change it to: SELECT dbo.Input_Hardware_Tickets.CostElement, dbo.Input_Hardware_Tickets.Frequency, dbo.HW_Ticket_Count.Total, cast(dbo.Input_Hardware_Tickets.Frequency as decimal(10,0)) / dbo.HW_Ticket_Count.Total AS Divide, The cast would need to be at least as large as your values get, so 10 is a safe bet, as that is the length of the maximum integer value in SQL Server. If you were using bigint as your type, I'd recommend moving it up a bit further.
Thank you very much! Exactly what I needed. I aprreciate your help!
