Struggling with Group by

Transact-SQL
1

Hey guys,

I am struggling with this query's results:

select ID 
,cast(case
When OD >= 0 and OD <0.49 then 0.5
When OD >= 0.5 and OD <0.99 then 1
When OD >= 1 and OD <1.49 then 1.5
When OD >= 1.5 and OD <1.99 then 2
When OD >= 2 and OD <2.49 then 2.5
When OD >= 2.5 and OD <2.99 then 3
When OD >= 3 and OD <3.49 then 3.5
When OD >= 3.5 and OD <3.99 then 4
When OD >= 4 and OD <4.49 then 4.5
When OD >= 4.5 and OD <4.99 then 5
When OD >= 5 and OD <5.49 then 5.5
When OD >= 5.5 and OD <5.99 then 6
else 7
end as float) as 'ODD'
,count(orderid) as 'Number of orders'
from ##temporderstable
group by ID,OD
order by ID,OD asc

The issue I have is that I am getting the same 'ODD' more than once for each ID.

I am currently getting for example
(ID, ODD, Number of orders)
100, 0.5, 1
100, 1, 1
100, 1, 1
200, 0.5, 2
200, 0.5, 1

I want to see for each ID one value per ODD and the number of orders next to it.
LIke this:
(ID, ODD, Number of orders)
100, 0.5, 1
100, 1, 2
200, 0.5, 3

Can anyone help please?

Thanks :slightly_smiling:

2

What is your input?

3

group by ID, cast(case When OD >= 0 and OD <0.49 then 0.5 When OD >= 0.5 and OD <0.99 then 1 When OD >= 1 and OD <1.49 then 1.5 When OD >= 1.5 and OD <1.99 then 2 When OD >= 2 and OD <2.49 then 2.5 When OD >= 2.5 and OD <2.99 then 3 When OD >= 3 and OD <3.49 then 3.5 When OD >= 3.5 and OD <3.99 then 4 When OD >= 4 and OD <4.49 then 4.5 When OD >= 4.5 and OD <4.99 then 5 When OD >= 5 and OD <5.49 then 5.5 When OD >= 5.5 and OD <5.99 then 6 else 7 end as float)

4

@stephen_hendricks, I had also wondered about that. But the OP was a bit confusing as to the problem.

5

Thanks for your assistance, I managed to get a colleague to help.

A nested select is the answer:

select d.ID, d.ODD, count(orderid)
from
(
select ID
,cast(case
When OD >= 0 and OD <0.49 then 0.5
When OD >= 0.5 and OD <0.99 then 1
When OD >= 1 and OD <1.49 then 1.5
When OD >= 1.5 and OD <1.99 then 2
When OD >= 2 and OD <2.49 then 2.5
When OD >= 2.5 and OD <2.99 then 3
When OD >= 3 and OD <3.49 then 3.5
When OD >= 3.5 and OD <3.99 then 4
When OD >= 4 and OD <4.49 then 4.5
When OD >= 4.5 and OD <4.99 then 5
When OD >= 5 and OD <5.49 then 5.5
When OD >= 5.5 and OD <5.99 then 6
else 7
end as float) as 'ODD'
,orderid
from ##temporderstable
) as d
group by ID,ODD
order by ID,ODD asc
6

Didn't work, sorry.

7

If od value is 0.49 your case statement returns 7. That can't be right.

I'm guessing you want to round up to nearest 0.5?
If that is correct, try this:

select id
      ,ceiling(od*2)/2 as [odd]
      ,count(orderid) as [number of orders]
  from #temporderstable
 group by id
         ,ceiling(od*2)/2
 order by id
         ,[odd]
1 Like
8

Why are you casting to float? Rounding errors will mess you up. Since you were rude and did not bother to post DDL, we do not know if your vague and poorly named "OD" is a float or a decimal. It looks like it ought to be a decimal to 2 or 3 places of precision. Want to try again and follow forum rules? :grinning:

1 Like
9

I guess I missed the memo :stuck_out_tongue_winking_eye:

In all honesty - how do you get the DDL? (and in particular for global/local temp tables)

Thanks for the heads up about casting to float...I'll try using decimal.

:+1:

10

I'll give this a go in the morning...but looks like exactly what I needed.

I knew there was a better way to do that than the case statement (which should actually be <0.5 rather than <0.49 anyway!)

11

So the ceiling method worked great @bitsmed.

Much appreciated :+1: