How to add results of two select commands in same query

Question

I currently have two select commands as per below. What I would like to do is to add the results together in the SQL query rather than the variables in code.

select sum(hours) from resource;
select sum(hours) from projects-time;

Is it possible to have both in the same SQL and output a sum of both results?

Solution 1

Yes. It is possible :D

SELECT  SUM(totalHours) totalHours
FROM
        ( 
            select sum(hours) totalHours from resource
            UNION ALL
            select sum(hours) totalHours from projects-time
        ) s

As a sidenote, the tablename projects-time must be delimited to avoid syntax error. Delimiter symbols vary on RDBMS you are using.

Solution 2

Something simple like this can be done using subqueries in the select clause:

select ((select sum(hours) from resource) +
        (select sum(hours) from projects-time)
       ) as totalHours

For such a simple query as this, such a subselect is reasonable.

In some databases, you might have to add from dual for the query to compile.

If you want to output each individually:

select (select sum(hours) from resource) as ResourceHours,
       (select sum(hours) from projects-time) as ProjectHours

If you want both and the sum, a subquery is handy:

select ResourceHours, ProjectHours, (ResourceHours+ProjecctHours) as TotalHours
from (select (select sum(hours) from resource) as ResourceHours,
             (select sum(hours) from projects-time) as ProjectHours
     ) t

Solution 3

UNION ALL once, aggregate once:

SELECT sum(hours) AS total_hours
FROM   (
   SELECT hours FROM resource
   UNION ALL
   SELECT hours FROM "projects-time" -- illegal name without quotes in most RDBMS
   ) x

Comments

[Rhys]

What is the s on the end? Is that a typo?

[Erwin Brandstetter]

@JW.: Being cautious since I don't know all .. :)

[John Woo]

@Rhys no, it's an ALIAS of the subquery. by the way, What RDBMS you are using? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc...

[Rhys]

Its for a SQLITE DB through JDBC conenction from a web app

[John Woo]

you also need to add double quotes around your table projects-time. like this, select sum(hours) totalHours from "projects-time"

[csharpsql]

I like this solution because it works for addition and subtraction

[halfbit]

I was using this very often ... UNION [ALL] ... it just did not come into my brain again ... I am getting old

[dougwoodrow]

This solution (first example) doesn't work if one of the sums is NULL.

[philipxy]

Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations.