Calculation Result Error (1 Viewer)

[ordnance1]

Wondering why the Expr1 column is not returning a decimal? 464/100 should return 4.64 and 334 should return 3.34. Ultimately, I would then like to round the number to the nearest whole number where 4.64 would = 5 and 3.34 would equal 3.

SELECT TOP (100) PERCENT dbo.StateMembership.Home_Club_Num, COUNT(dbo.StateMembership.Member_Number / 100) AS DelegateLimit, CONVERT(Decimal(5, 2), COUNT(dbo.StateMembership.Member_Number) / 100) AS Expr1
FROM   dbo.StateMembership INNER JOIN
             dbo.StateMembership AS StateMembership_1 ON dbo.StateMembership.Member_ID = StateMembership_1.Member_ID LEFT OUTER JOIN
             dbo.vw_Aeries_DelegateCount ON StateMembership_1.Home_Club_Num = dbo.vw_Aeries_DelegateCount.AerieNumber
GROUP BY dbo.StateMembership.Home_Club_Num

ORDER BY dbo.StateMembership.Home_Club_Num

 

[theDBguy]

Not sure if this is it, but DelegateLimit is counting the number of members after dividing it by 100. Whereas, Expr1 is counting only the number of members before dividing it by 100.

 

[plog]

So much of your SQL is just wonky. As DbGuy pointed out:

COUNT(dbo.StateMembership.Member_Number / 100)

You are simply counting the number of records, that division is doing nothing for you. You could change it to any mathematical operation and constant and the result would be the same ( + 512, -316, *4.7). You are not dividing your COUNT by 100, but simply dividing by 100 then counting (not summing, but couting).

SELECT TOP (100) PERCENT

That does nothing, right? 100% of the results are all the results which you would get if you didn't even use TOP.

FROM   dbo.StateMembership INNER JOIN
             dbo.StateMembership AS StateMembership_1 ON dbo.StateMembership.Member_ID = StateMembership_1.Member_ID

So you linked the table to itself by the exact same field? But nowhere else in your query do you use any fields from StateMembership_1. The only effect that could have is to multiply the results in your final query assuming Member_ID isn't unique. Is that the purpose?

LEFT OUTER JOIN
             dbo.vw_Aeries_DelegateCount ON StateMembership_1.Home_Club_Num = dbo.vw_Aeries_DelegateCount.AerieNumber

Again, you are linking another datasource that you use no where else. The only effect this can have is to multiply the results in your final query. Is that the purpose?

Even if you got the exact result you expected, with all those weird JOINS I would still select a specific Home_Club_Num and manually calculate the result to ensure what the query produces is the actual result you want.

 

[ordnance1]

Not sure if this is it, but DelegateLimit is counting the number of members after dividing it by 100. Whereas, Expr1 is counting only the number of members before dividing it by 100.

Okay so I am at a loss. MemberCount returns the total number of members for each club. Each Club is entitled to 1 delegate per 100 members. So what is the best way to divided the total number of members to get by 100 to get the decimal result. Where 331 would equal 3.31 and 464 would equal 4.64?
I would then need to round the result to the nearest whole number. 3.31 to 3 and 464 to 5.

SELECT Home_Club_Num, COUNT(Member_Number) AS MemberCount
FROM   dbo.StateMembership
GROUP BY Home_Club_Num
Order By Home_Club_Num

 

[The_Doc_Man]

SELECT Home_Club_Num,  COUNT( Member_Number ) / 100 As MemberCount
FROM dbo.StateMembership
GROUP BY Home_Club_Num
ORDER BY Home_Club_Num ;

You CAN include the ROUND function, but that uses Banker's Rounding so that it would favor rounding to the nearest even number. Or you can search the forum for "Rounding" to see some custom functions others have created to implement other kinds of rounding.

 

[ordnance1]

SELECT Home_Club_Num,  COUNT( Member_Number ) / 100 As MemberCount
FROM dbo.StateMembership
GROUP BY Home_Club_Num
ORDER BY Home_Club_Num ;

You CAN include the ROUND function, but that uses Banker's Rounding so that it would favor rounding to the nearest even number. Or you can search the forum for "Rounding" to see some custom functions others have created to implement other kinds of rounding.

But I need COUNT( Member_Number ) / 100 to return the decimal value. 464/100 = 4.64 not 4

 

[The_Doc_Man]

Then don't round. Force the count to be a single (or a double) before you do the divide.

SELECT Home_Club_Num,  CSNG( COUNT( Member_Number ) ) / 100 As MemberCount
FROM dbo.StateMembership
GROUP BY Home_Club_Num
ORDER BY Home_Club_Num ;

 

[Minty]

Use

SELECT Home_Club_Num,  CSNG( COUNT( Member_Number ) ) / 100.0 As MemberCount
FROM dbo.StateMembership
GROUP BY Home_Club_Num
ORDER BY Home_Club_Num ;

And you'll get a decimal or floating point answer.

Note the use of 100.0 - it forces the result to the correct data format to return a decimal.
By using 100 you are implying you want an integer answer.

 

[ordnance1]

Thank you all for the assistance. Final code is:

SELECT TOP (100) PERCENT Home_Club_Num, COUNT(Member_Number) AS MemberCount, CASE WHEN ROUND(CAST(COUNT(Member_Number) AS Decimal(7, 2)) / 100, 0) = 0 THEN 1 ELSE ROUND(CAST(COUNT(Member_Number) AS Decimal(7, 2)) / 100, 0) END AS DelegateAllowance
FROM   dbo.StateMembership
GROUP BY Home_Club_Num
ORDER BY Home_Club_Num

 

[The_Doc_Man]

Just be aware that the native ROUND function uses Banker's Rounding. Otherwise, that looks like a reasonable approach.

 

[ordnance1]

Just be aware that the native ROUND function uses Banker's Rounding. Otherwise, that looks like a reasonable approach.

Not sure about Bankers rounding, but it rounded correctly for my needs:
6.84 to 7
3.17 to 3

 

[theDBguy]

Not sure about Bankers rounding, but it rounded correctly for my needs:
6.84 to 7
3.17 to 3

Re: Banker's Rounding

If you had the following member counts, how would you like them rounded?

350
450

Just curious...

 

[ordnance1]

Hi,

Thanks for asking. In my code 350 becomes 3.5 and 450 becomes 4.5, and I would want them to round up.

 

[theDBguy]

Hi,

Thanks for asking. In my code 350 becomes 3.5 and 450 becomes 4.5, and I would want them to round up.

Ah, thanks for the clarification. Unfortunately, banker's rounding will result in both of those values rounding up/down to 4. Give it a try and let us know if that's not what happens with you (in case I misunderstood it). Cheers!

 

[ordnance1]

Had no clubs with memberships ending in 50 so added a couple of fake members to a club to bring them up to 450 which of course my code converts to 4.5 and it rounded to 5.

 

[theDBguy]

Had no clubs with memberships ending in 50 so added a couple of fake members to a club to bring them up to 450 which of course my code converts to 4.5 and it rounded to 5.

Then you should be safe. Good luck with your project.