Yeah, something like SELECT order_id,issued,reference, company, department_id, SUM(quantity) AS quantity, SUM(dispatch) AS dispatch, SUM(surplus) AS surplus
FROM orders_surplus GROUP BY order_id I have however to sum quantity, dispatch and surplus fields to force the view to calculate the right values to get that this 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" becomes this order_id issued company dep_id qty, dispatch surplus > "1" "12-11-2013" "Siemens" "1" "8" "6" "2" that is I want the total sum of quantity field (6+2=8), the total sum of the dispatch field (4+2=2) and the result of their subtraction (8-6=2) thanks jim On Thu, Nov 14, 2013 at 2:35 PM, Jim Callahan < jim.callahan.orla...@gmail.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
