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
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"
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
