i trying count non-null values 3 columns. however, same count returned each of 3 columns despite having differing number of non-null values. i'm not sure why case (do need recast column data type? wrong sql clause?). below i've tried , results.
edit: replaced images text. made shorter aliases.
select cast(users.install_ts date) inst_date, count(users.install_ts) total_inst, count(users.firstlogin_ts) total_first_logins, count(users.firstpurchase_ts) conv_cust, sum(case when users.firstpurchase_ts null 1 else 0 end) conv_cust users group cast(users.install_ts date) inst_date total_inst total_first_logins conv_cust conv_cust ---------- ---------- ------------------ --------- --------- 2015-01-01 17191 17191 17191 0 2015-01-02 41038 41038 41038 0 2015-01-03 41176 41176 41176 0 2015-01-04 41072 41072 41072 0 2015-01-05 41115 41115 41115 0 2015-01-06 8417 8417 8417 0 2015-05-16 9991 9991 9991 0 here users table:
uid device_id install_ts firstlogin_ts firstpurchase_ts ------ ---------- ------------------- ------------------- ------------------- 121045 galaxys3 2015-01-01 14:00:01 2015-01-01 14:00:01 (null) 121046 galaxys1 2015-01-01 14:00:03 2015-01-01 14:00:07 2015-01-02 06:00:07 121047 iphone3 2015-01-01 14:00:03 2015-01-01 14:00:03 (null) 121048 galaxys1 2015-01-01 14:00:04 (null) (null) 121049 iphone5 2015-01-01 14:00:07 2015-01-01 14:00:08 (null) 121050 ipad4 2015-01-01 14:00:07 2015-01-01 14:00:09 (null) 121051 iphone4s 2015-01-01 14:00:11 (null) (null) 121052 iphone4s 2015-01-01 14:00:13 (null) (null) 121053 galaxytab3 2015-01-01 14:00:16 (null) (null) 121054 iphone4 2015-01-01 14:00:19 2015-01-01 14:00:22 (null) 121055 ipad1 2015-01-01 14:00:22 2015-01-01 14:00:26 (null) 121056 ipad2 2015-01-01 14:00:26 2015-01-01 14:00:29 (null) 121057 galaxytab2 2015-01-01 14:00:30 2015-01-01 14:00:31 (null) 121058 iphone5s 2015-01-01 14:00:34 2015-01-01 14:00:38 (null) 121059 galaxys5 2015-01-01 14:00:34 (null) (null) 121060 galaxys5 2015-01-01 14:00:34 2015-01-01 14:00:38 (null) 121061 galaxys5 2015-01-01 14:00:37 (null) (null) 121062 iphone3 2015-01-01 14:00:39 (null) (null) description of table:
describe users field type null key default ---------------- ----------- ---- --- ------------------- --------------------------- uid int(11) yes mul (null) device_id varchar(64) yes (null) install_ts timestamp no current_timestamp on update current_timestamp firstlogin_ts timestamp no 0000-00-00 00:00:00 firstpurchase_ts timestamp no 0000-00-00 00:00:00 edit: doing them 1 @ time gives same results. maybe grouping problem. if that's case, how go achieving intended results?
one @ time:
select count(users.firstlogin_ts) total_first_logins users group cast(users.install_ts date) total_first_logins 17191 41038 41176 41072 41115 8417 9991 another column:
select count(users.install_ts) total_inst users group cast(users.install_ts date) total_inst 17191 41038 41176 41072 41115 8417 9991
i guessing there might display issue @ root of problem. data present shows (null), table description indicates fields not nullable in first place. however, fields default '0000-00-00 00:00:00', not quite valid value type, allowed (it's weird). apis convert such values null, though not null in database. try instead:
select cast(users.install_ts date) inst_date, count(1) total_inst, count(nullif(users.firstlogin_ts, '0000-00-00 00:00:00')) total_first_logins, count(nullif(users.firstpurchase_ts, '0000-00-00 00:00:00')) conv_cust users group cast(users.install_ts date) nullif convert such values true null values.
Comments
Post a Comment