I am trying to come up with a single SQL statement that will print out

[sleestak]

I am trying to come up with a single SQL statement that will print out a list of addresses grouped by even and odd addresses. My DB has field called house_number which is a VARCHAR2, so I am using the last character of that field to determine if it is even/odd. Unfortunately, the last character is not always a digit, so the mod function won't work. BTW, this is in Oracle 8. What I have right now is:<br>
<br>
select ALL count(*), decode(mod(substr(house_number,-1),2),0,'even','odd') even_odd, house_number from member where member_id&gt;589990 group by decode(mod(substr(house_number,-1),2),0,'even','odd'), house_number;<br>
<br>
There are two problems with this:<br>
1) The mod problem mentioned above.<br>
2) Even though, I specified ALL...it is really doing a DISTINCT in that like house numbers are being grouped together.<br>
<br>
Any suggestions would be helpful.

 

[carp]

sleestak -<br>
<br>
It seems to me that if you add <br>
<br>
AND substr(house_number,-1) IN ('0','1','2','3','4','5','6','7','8','9') <br>
<br>
to your WHERE clause, you can eliminate the addresses that don't end with a digit. Of course, this also means<br>
that those houses won't be included in your final count.<br>
<br>
When you specify <br>
<br>
GROUP BY ..., house_number<br>
<br>
then all like house numbers are going to be grouped together. However, if you have two houses with the same<br>
number (e.g., 12345), then you should get a result of<br>
<br>
count(*) even_odd house_number<br>
2 odd 12345<br>
<br>
Are you getting something different?