question

hafsa avatar image
hafsa asked

problem with groupby

HI, Can someone plz help me... i have this piece of code which is displayin some part of my view ----code ---------- select count(distinct Numorders) as NumEmp, -->>>its not counting distinct values... ( case when (numorders%10)<=5 -->>>>Rounding the last digit to 0 if then numorders-(numorders%10) -->>>>last digit is lesser than 5 else numorders+(10-(numorders%10)) -->>>>else if last digit>5 then end -->>making it celing format )as Numorders from myview group by Numorders -->>>>>>grouping on Numorders ------it gives this output on selecting all columns..... numemp numorders 1 40 1 40 1 70 1 70 1 100 1 100 1 120 1 130 1 160 but i require this output.. numemp numorders 2 40 2 70 2 100 1 120 1 130 1 160 if u check out my code you will see that i have included a group by stmt on Numorders also...but still im not getting the required output...plz help thanks in advance
sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
The Group By & Count Distinct clauses are looking at the underlying table field "numorders" rather than your calculated field. Replace "numorders" with your CASE calculation in both these places, and you should be away.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image
Oleg answered
My answer is a complete steal of the one by Thomas, but, in my defense, it does include a complete query (which is by the way somewhat simpler than the original query in question). I would also like to elaborate on a couple of seemingly important details. - The logic you use to round the input numbers is a bit expensive - The logic you use to get your rounded numbers is weird at best as it is almost compliant with usual rounding rules except it rounds the number ending with 5 down instead of rounding it up as any normal logic would dictate. In other words, you could simply use the round function directly, but because your requirements have a twist, you shoud decrease the original number by 1 and only then apply the round function. To make your query work as expected, you can do something like this: select count(1) numemp, round(numorders - 1, -1) numorders from myview group by round(numorders - 1, -1) order by 2; How it works: the second argument of the round function dictates the precision, so passing **-1** will round the input to the nearest 10. The **numorders - 1** instead of the logically expected **numorders** as the first parameter is simply to adapt to your strange rounding requirement. If your original values in myview were something like 38, 45, 67, 75, 101, 96, 115, 134, 164 then you will get result like this: numemp numorders ----------- ----------- 2 40 2 70 2 100 1 110 1 130 1 160 Oleg
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Oleg - you are forgiven!
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@ThomasRushton I do thank you, kind Sir.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+1 - although would be worth specifying that T-SQLs ROUND uses normal mathematical rounding, rather than banker's rounding - like .NET's Math.Round does.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@Matt Whitfield Good point. .NET's Math.Round is more flexible in that it can do it **MidpointRounding.AwayFromZero** meaning that 1.5 rounds to 2 and 2.5 rounds to 3 or it can do it **MidpointRounding.ToEven** when both 1.5 and 2.5 are rounded to 2. On the other hand, the decimals parameter in Math.Round cannot take negative values while T-SQL does, so with **Math.Round** it would take to divide the input by 10.0 before the Round is called, multiply it by by 10 after, and then cast it as int just to get rounded to the nearest ten result in T-SQL by simply passing -1 as a second parameter to round function.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.