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.
Instead of
.5
as the false condition of theIF()
, use0.5
.I believe what is happening is that after the
GROUP BY
is evaluated, the values in the conditional column must be of the same data type. SinceSELECT
is evaluated afterGROUP BY
, theIF()
is converting the returned values in favor of an integer (from the boolean expression), and thus your0.5
gets rounded up to1
but if you explicitly put a0
in front of the decimal place, theIF()
will treat returned values as a decimal including the result of the boolean expression (i.e.1.0
or0.0
).Or you could even put single quotes around the
.4
so that the column values will be treated as strings, so some values would appear to be integers and some decimals. The values should be automatically converted when used in numerical contexts (i.e.SELECT ('2.5' * 3.5) AS test #8.75
).