0

I'm getting the Truncated incorrect INTEGER value error in MYSQL, when I execute the code below. I know the logic doesn't make much sense and that will change and I am more concert about the error. I have tried casting the whole case statement but I still get the same error message.

Here is the original statement

select ( count(case when v.SalesDate = v.SalesDate then v.Surname end) ) as x
from finaljoinalldata v group by date(v.SalesDate) order by date(v.SalesDate);

I have tried also

select ( count(CAST(case when v.SalesDate = v.SalesDate then v.Surname end) AS SIGNED) ) as x
from finaljoinalldata v group by date(v.SalesDate) order by date(v.SalesDate);

Any help will be appreciated. Thanks

driveinmotors
  • 1
  • 2

1 Answers1

0

Your statement doesn't make sense. Why do you have the case condition anyway? It is redundant -- unless you are specifically checking for NULL values.

This should work:

select count(v.Surname) as x
from finaljoinalldata v
group by date(v.SalesDate)
order by date(v.SalesDate);

I am suspicious when I see a group by and the group by columns are not in the select.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The logic is more than just that, it will have a second sub-select and a join. I am not concern about the logic because on the full query I get still the same error and I have narrow it down to the statement ( count(case when v.SalesDate = v.SalesDate then v.Surname end) ) as x , the case statement by itself runs fine but as soon as add the count to it it throws the error. Let me know it that makes sense. – driveinmotors Apr 18 '15 at 00:01
  • I removed the group by clause it still wont work select ( count(case when v.SalesDate = v.SalesDate then v.Surname end) ) as x from finaljoinalldata v – driveinmotors Apr 18 '15 at 00:15
  • In case you want to see the whole query I am trying to run is the following: select date(v.SalesDate), count(case when v.SalesDate = vv.minva then v.Surname end) as num_new_users, (count(distinct v.Surname) - count(case when v.SalesDate = vv.minva then v.Surname end) ) as num_repeat_users from finaljoinalldata v join (select t.Surname, min(SalesDate) as minva from finaljoinalldata t group by Surname ) vv on v.Surname = vv.Surname group by date(v.SalesDate) order by date(v.SalesDate); – driveinmotors Apr 18 '15 at 00:32
  • @driveinmotors . . . These problems don't make sense. What happens if you replace the `count()` with `sum(case when . . . then 1 else 0 end)`. – Gordon Linoff Apr 18 '15 at 01:54