How to query and/or group complex SQL? Add the language to create a SQL VIEW before your SELECT statement:
CREATE VIEW viewname AS SELECT [your SQL] http://www.sqlite.org/lang_createview.html Then use the viewname in a second SELECT statement as you would a table. SELECT order_id, issued, company, dep_id, qty, dispatch, surplus FROM viewname WHERE order_id = 1; or, if necessary WHERE order_id = '1'; How to query and/or group: ...complex SQL... order_id issued company dep_id qty, dispatch surplus > "1" "12-11-2013" "Siemens" "1" "6" "4" "2" > "1" "12-11-2013" "Siemens" "1" "2" "2" "0" > "2" "13-11-2013" "Siemens" "2" "10" "10" "0" > "3" "13-11-2013" "Siemens" "8" "3" "3" "0" > How I can group by order_id? In the example I'would return on order_id =1: I assume your SQL is already producing the output in your example. Hope I haven't missed your point and this helps. Jim On Thu, Nov 14, 2013 at 2:48 AM, Giuseppe Costanzi < giuseppecosta...@gmail.com> wrote: > hi to everybody, > you excuse for the preceding mails but I have had problems with this and I > have had to change provider. > However I propose my question. > I have this query, that you also see in attachment file. > 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, > > order_details.quantity - SUM(CASE WHEN transactions.category_id = 1 > THEN 1 ELSE 0 END) AS surplus > > FROM orders > INNER JOIN departments ON (departments.department_id = > orders.department_id) > > INNER JOIN suppliers ON (suppliers.supplier_id = orders.supplier_id) > INNER JOIN order_details ON (orders.order_id = order_details.order_id) > INNER JOIN transactions ON order_details.order_detail_id = > transactions.order_detail_id > > WHERE orders.state = 0 AND orders.enable =1 > GROUP BY order_details.order_detail_id > > that return such as > > order_id issued company dep_id qty, dispatch surplus > "1" "12-11-2013" "Siemens" "1" "6" "4" "2" > "1" "12-11-2013" "Siemens" "1" "2" "2" "0" > "2" "13-11-2013" "Siemens" "2" "10" "10" "0" > "3" "13-11-2013" "Siemens" "8" "3" "3" "0" > > How I can group by order_id? In the example I'would return on order_id > =1: > > order_id issued company dep_id qty, dispatch surplus > "1" "12-11-2013" "Siemens" "1" "8" "6" "2" > > any suggestions? > > regards beppe > > _______________________________________________ > 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
