select query with sum value from another table

Question

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 table where x would be the project id from the first select

SELECT sum(invoice_net)
FROM invoice
WHERE projectID= x

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

SELECT sum(total_order)
FROM order
WHERE projectID = x

score 1 · Accepted Answer · edited May 23 '17 at 12:07

You didn't provide sample data or even enough of your DB schema for an answer to provide the exact SQL you need.

You don't describe the invoice or order tables. I have to assume they contain a ProjectID attribute.

You could use a subselect:

http://www.techrepublic.com/article/use-sql-subselects-to-consolidate-queries/1045787/ https://msdn.microsoft.com/en-us/library/ff487138.aspx

You're select might look something like (untested):

SELECT
    project.projectID,
    project.project_title,
    project.end_date,
    project.project_manager,
    project_status.project_status,
    (SELECT sum(invoice.invoice_net) FROM invoice WHERE invoice.projectID = project.projectID),
    (SELECT sum(order.total_order) FROM order WHERE order.projectID = project.projectID)
FROM project
LEFT JOIN project_status
    ON project.project_statusID=project_status.project_statusID
WHERE project.project_statusID BETWEEN 1 AND 12

Or a Join with a Group By. See:

SQL JOIN, GROUP BY on three tables to get totals

You'll have to group by all the non-aggregated (in general this will be all the fields you select from your project table), then apply an aggregate function (sum) to your detail attributes (invoice_net and total_order).

Like this (untested):

SELECT
    project.projectID,
    project.project_title,
    project.end_date,
    project.project_manager,
    project_status.project_status,
    sum(invoice.invoice_net),
    sum(order.total_order)
FROM project
LEFT JOIN project_status
    ON project.project_statusID=project_status.project_statusID
LEFT JOIN invoice
    ON project.projectID = invoice.projectID
LEFT JOIN order
    ON project.projectID = order.projectID
WHERE project.project_statusID BETWEEN 1 AND 12
GROUP BY project.projectID, project.project_title, project.end_date,
    project.project_manager, project_status.project_status

Which to choose? Performance should be comparable, but it's possible a weakness in the query optimizer could lead to one being favored over the other. Test performance if this is important.

For a one-off, I'd probably use the subselect, if a view of the joined tables didn't already exist. But in practice a view does or should exist, in which case using that (or creating the view, if needed), with GROUP BY, is pretty natural.

So the real answer becomes (untested):

CREATE VIEW project_order_invoice AS
SELECT
    project.projectID,
    project.project_title,
    project.end_date,
    project.project_manager,
    project_status.project_status,
    invoice.invoice_net,
    order.total_order
FROM project
LEFT JOIN project_status
    ON project.project_statusID=project_status.project_statusID
LEFT JOIN invoice
    ON project.projectID = invoice.projectID
LEFT JOIN order
    ON project.projectID = order.projectID


SELECT
    projectID,
    project_title,
    end_date,
    project_manager,
    project_status,
    sum(invoice_net),
    sum(total_order)
FROM project_order_invoice
WHERE project_statusID BETWEEN 1 AND 12
GROUP BY projectID, project_title, end_date,
    project_manager, project_status

For the view, you'll probably want to include all the attributes from all the tables, except only 1 copy of the ID attributes.

Thanks, I'll read up on subselects. Yes both related have a projectID field — JK36, Oct 05 '15 at 20:56

select query with sum value from another table

1 Answers1