Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
1  
Nice work. Thanks for that. – Rhys Mar 1 at 3:12
you're welcome :D – JW 웃 Mar 1 at 3:13
What is the s on the end? Is that a typo? – Rhys Mar 1 at 3:14
@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... – JW 웃 Mar 1 at 3:15
Its for a SQLITE DB through JDBC conenction from a web app – Rhys Mar 1 at 3:17
show 2 more comments
up vote 4 down vote

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
share|improve this answer
up vote 4 down vote

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
share|improve this answer
not most but all :) – JW 웃 Mar 1 at 3:14
1  
@JW.: Being cautious since I don't know all .. :) – Erwin Brandstetter Mar 1 at 3:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.