[use [email protected] for usage questions]
On May 9, 2012, at 11:25 PM, Domingo Alvarez Duarte wrote:
> 2012-05|-1
> 2012-05|24 <<<<<<< this was not expected
What where you expecting?
The group by apply to the second part of your union. The union itself, well,
union both result sets. So it would appear that you got exactly what you asked
for.
Alternatively, if you meant to sum it all up:
select strftime( '%Y-%m', order_date ) as qm,
sum
(
case order_type_id
when 2 then -1
else 1
end
) as count
from orders
where order_type_id in ( 1, 2 )
and order_date >= date('now', '-12 months')
group by qm
order by qm desc;
Begin forwarded message:
> From: Domingo Alvarez Duarte <[email protected]>
> Subject: [sqlite-dev] Bug in union with group by
> Date: May 9, 2012 11:25:41 PM GMT+02:00
> To: sqlite-dev <[email protected]>
>
> Hello !
>
> I found a bug in sqlite that can be seem with this small sql code:
>
> begin;
> create table orders(order_date date, order_type_id integer);
> insert into orders values("2011-04-01", 1);
> insert into orders values("2011-04-01", 1);
> insert into orders values("2011-04-01", 2);
>
> insert into orders values("2011-05-01", 1);
> insert into orders values("2011-05-01", 1);
> insert into orders values("2011-05-01", 2);
>
> insert into orders values("2011-06-01", 1);
> insert into orders values("2011-06-01", 1);
> insert into orders values("2011-06-01", 2);
>
> insert into orders values("2011-07-01", 1);
> insert into orders values("2011-07-01", 1);
> insert into orders values("2011-07-01", 2);
>
> insert into orders values("2011-08-01", 1);
> insert into orders values("2011-08-01", 1);
> insert into orders values("2011-08-01", 2);
>
> insert into orders values("2011-09-01", 1);
> insert into orders values("2011-09-01", 1);
> insert into orders values("2011-09-01", 2);
>
> insert into orders values("2011-10-01", 1);
> insert into orders values("2011-10-01", 1);
> insert into orders values("2011-10-01", 2);
>
> insert into orders values("2011-11-01", 1);
> insert into orders values("2011-11-01", 1);
> insert into orders values("2011-11-01", 2);
>
> insert into orders values("2011-12-01", 1);
> insert into orders values("2011-12-01", 1);
> insert into orders values("2011-12-01", 2);
>
> insert into orders values("2012-01-01", 1);
> insert into orders values("2012-01-01", 1);
> insert into orders values("2012-01-01", 2);
>
> insert into orders values("2012-02-01", 1);
> insert into orders values("2012-02-01", 1);
> insert into orders values("2012-02-01", 2);
>
> insert into orders values("2012-03-01", 1);
> insert into orders values("2012-03-01", 1);
> insert into orders values("2012-03-01", 2);
>
> insert into orders values("2012-04-01", 1);
> insert into orders values("2012-04-01", 1);
> insert into orders values("2012-04-01", 2);
>
> insert into orders values("2012-05-01", 1);
> insert into orders values("2012-05-01", 1);
> insert into orders values("2012-05-01", 2);
>
> commit;
>
> select strftime('%Y-%m', order_date) as qm, count(*) as q
> from orders where order_type_id =1
> and order_date >= date('now', '-12 months')
> UNION
> select strftime('%Y-%m', order_date) as qm, -count(*) as q
> from orders where order_type_id =2
> and order_date >= date('now', '-12 months')
>
> group by qm
> order by qm desc;
>
> The output using sqlite3 shell is:
>
> $ sqlite3.exe test.db < sum-group.sql
> 2012-05|-1
> 2012-05|24 <<<<<<< this was not expected
> 2012-04|-1
> 2012-03|-1
> 2012-02|-1
> 2012-01|-1
> 2011-12|-1
> 2011-11|-1
> 2011-10|-1
> 2011-09|-1
> 2011-08|-1
> 2011-07|-1
> 2011-06|-1
> _______________________________________________
> sqlite-dev mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
