I'm using mysql version 5.1.41-3ubuntu12.10 (Ubuntu).
mysql> show create table tt\G
*************************** 1. row ***************************
Table: tt
Create Table: CREATE TABLE `tt` (
`pz` int(8) DEFAULT NULL,
`os` varchar(8) DEFAULT NULL,
`uz` int(11) NOT NULL,
`p` bigint(21) NOT NULL DEFAULT '0',
`c` decimal(23,0) DEFAULT NULL,
KEY `pz` (`pz`),
KEY `uz` (`uz`),
KEY `os` (`os`),
KEY `pz_2` (`pz`,`uz`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select pz,uz,pz*uz,
-> if(pz*uz,1,.5),
-> left(pz,2) pl,left(lpad(uz,5,0),2) ul,
-> p from tt limit 10;
+-------+----+-------+----------------+--------+----+--------+
| pz | uz | pz*uz | if(pz*uz,1,.5) | pl | ul | p |
+-------+----+-------+----------------+--------+----+--------+
| NULL | 0 | NULL | 0.5 | NULL | 00 | 4080 |
| NULL | 0 | NULL | 0.5 | NULL | 00 | 323754 |
| 89101 | 0 | 0 | 0.5 | 89 | 00 | 6880 |
| 0 | 0 | 0 | 0.5 | 0 | 00 | 11591 |
| 89110 | 0 | 0 | 0.5 | 89 | 00 | 72 |
| 78247 | 0 | 0 | 0.5 | 78 | 00 | 27 |
| 90062 | 0 | 0 | 0.5 | 90 | 00 | 5 |
| 63107 | 0 | 0 | 0.5 | 63 | 00 | 4 |
| NULL | 0 | NULL | 0.5 | NULL | 00 | 54561 |
| 94102 | 0 | 0 | 0.5 | 94 | 00 | 12499 |
+-------+----+-------+----------------+--------+----+--------+
So far so good. As you see, 0.5 appears as a value of if(pz*uz,1,.5). The problem is:
mysql> select os,
-> if(pz*uz,left(pz,2)<=>left(lpad(uz,5,0),2),.5) uptwo,
-> if(pz*uz,left(pz,3)<=>left(lpad(uz,5,0),3),.5) upthree,
-> sum(p) p,sum(c) c
-> from tt t
-> group by os,uptwo,upthree order by null;
+----+-------+---------+---------+-------+
| os | uptwo | upthree | p | c |
+----+-------+---------+---------+-------+
| u | 1 | 1 | 52852 | 318 |
| i | 1 | 1 | 7046563 | 21716 |
| m | 1 | 1 | 1252166 | 7337 |
| i | 0 | 0 | 1830284 | 4033 |
| m | 0 | 0 | 294612 | 1714 |
| i | 1 | 0 | 911486 | 3560 |
| m | 1 | 0 | 145182 | 1136 |
| u | 0 | 0 | 12144 | 23 |
| u | 1 | 0 | 1571 | 8 |
+----+-------+---------+---------+-------+
Although I group by uptwo, 0.5 doesn't appear in that column. What happened to the 0.5 values?
Edit: As noted in the comments to Todd Gibson's answer, I also tried it with
if(pz*uz,cast(left(pz,2)<=>left(lpad(uz,5,0),2) as decimal),.5) instead of
if(pz*uz,left(pz,2)<=>left(lpad(uz,5,0),2),.5), but it, too, didn't work.
