lundi 5 octobre 2015

select query with sum value from another table

I'm working with SQLite and have 3 tables, project, invoice and order. I have a select statement based on the project table which picks off the fields stored in this table, however I would also like the sum value of invoices for a particular project and the sum value of orders for a particular project. I know how to get all the values individually, however I haven't figured out how to get the Sum value of orders/invoices into the project data row. Is it a nested select I need to search for, or another concept?

Select based on project table

SELECT  project.projectID, project.project_title, project.end_date, project.project_manager, project_status.project_status
FROM project
LEFT JOIN project_status
ON project.project_statusID=project_status.project_statusID
WHERE project.project_statusID BETWEEN 1 AND 12

Select based on invoice where x would be the project id from the first select

SELECT sum(invoice_net)
FROM invoice
WHERE projectID= x

Select based on orders where x would be the project id from the first select

SELECT sum(total_order)
FROM order
WHERE projectID = x

Aucun commentaire:

Enregistrer un commentaire