I can confirm this with the following script: SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t (a, b); sqlite> insert into t values (1, 't'); sqlite> insert into t values (1, 't'); sqlite> insert into t values (1, 'T'); sqlite> insert into t values (2, 't'); sqlite> insert into t values (2, 'T'); sqlite> insert into t values (2, 'T'); sqlite> select a, count(case when b = 't' then 1 end), count(case when b = 'T' then 1 end) from t group by a; 1|2|2 2|1|1 sqlite> select a, count(case when b = 't' then 1 end), count(case when b = 'T' then 2 end) from t group by a; 1|2|1 2|1|2 sqlite>
Pavel On Tue, Oct 11, 2011 at 11:30 AM, Nelson, Bjorn <bjorn.nel...@morganstanley.com> wrote: > I am having an issue where I build a query that creates columns based on case > expressions. The expressions are identical except for a case difference. > The result only shows data from the first expression duplicated in both > columns. > > The query that results in the issue: > select > mycategory, > count(case when type = 'typea' then 1 end) as 'test1', > count(case when type = 'Typea' then 1 end) as 'test2' > from mytable > group by mycategory > > Results in a table like this: > mycategory|test1|test2 > cat1|500|500 > cat2|350|350 > > But if I change it to: > select > mycategory, > count(case when type = 'typea' then 1 end) as 'test1', > count(case when type = 'Typea' then 2 end) as 'test2' > from mytable > group by mycategory > > Results in a table like this: > mycategory|test1|test2 > cat1|500|200 > cat2|350|150 > > It's as if, even though I specify an AS clause, it is representing the column > header internally as the expression (ignoring the unique name I provided), > and since columns are case insensitive then it just refers to the first > column when printing it out. > > Bjorn Nelson > > -------------------------------------------------------------------------- > NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions > or views contained herein are not intended to be, and do not constitute, > advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform > and Consumer Protection Act. If you have received this communication in > error, please destroy all electronic and paper copies and notify the sender > immediately. Mistransmission is not intended to waive confidentiality or > privilege. Morgan Stanley reserves the right, to the extent permitted under > applicable law, to monitor electronic communications. This message is subject > to terms available at the following link: > http://www.morganstanley.com/disclaimers. If you cannot access these links, > please notify us by reply message and we will send the contents to you. By > messaging with Morgan Stanley you consent to the foregoing. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
