[sqlite] GROUP BY on SUM CASE

2013-11-13 Thread g . costanzi
hi, I've this SELECT orders.order_id AS order_id, strftime('%d-%m-%Y', orders.issued) AS issued, suppliers.company AS company, departments.department_id AS dep_id, order_details.quantity AS qty, SUM(CASE WHEN transactions.category_id = 1 THEN 1 ELSE 0 END) AS dispatch,

Re: [sqlite] Difference between two dates fails

2009-11-20 Thread g . costanzi
- Original Message Da: "General Discussion of SQLite Database" sqlite-users@sqlite.org To: "General Discussion of SQLite Database" sqlite-users@sqlite.org Oggetto: Re: [sqlite] Difference between two dates fails Data: 20/11/09 16:22 On Nov 20, 2009, at

Re: [sqlite] Difference between two dates fails

2009-11-20 Thread g . costanzi
If I use SELECT ProductID,OrderDate ,DispatchDate, julianday(tblOrders.DispatchDate) - julianday(tblOrders.OrderDate) FROM tblOrders WHERE ProductID = 2195 AND OrderState IN (0,10) it work fine 1,(2195, u'2008-09-02', u'2008-09-12', 10.0) 2,(2195, u'2008-09-17', u'2008-09-29', 12.0)

[sqlite] Difference between two dates fails

2009-11-20 Thread g . costanzi
Hi,why? SELECT ProductID,OrderDate ,DispatchDate, strftime('%d',tblOrders.DispatchDate) - strftime('%d',tblOrders.OrderDate) FROM tblOrders WHERE ProductID = 2195 AND OrderState IN (0,10) returns 1,(2195, u'2008-09-02', u'2008-09-12', 10) 2,(2195, u'2008-09-17', u'2008-09-29', 12)