HI clemens I've resolve with
SELECT order_id,issued,reference, company, dep_id, SUM(qty) AS quantity,
SUM(dispatch) AS dispatch, SUM(surplus) AS surplus
FROM(
SELECT
orders.order_id AS order_id,
orders.reference AS reference,
strftime('%d-%m-%Y', orders.issued) AS issued,
suppliers.company AS company,
orders.department_id AS dep_id,
order_details.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 suppliers ON (suppliers.supplier_id = orders.supplier_id)
INNER JOIN order_details ON (orders.order_id = order_details.order_id)
LEFT 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)
GROUP BY order_id
On Thu, Nov 14, 2013 at 11:04 AM, Clemens Ladisch <[email protected]>wrote:
> Giuseppe Costanzi wrote:
> > 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?
>
> By writing "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"
>
> Also add SUM(...) where needed.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
