CODEWITHSUNDEEP
sqlsqliteselect
Rhys
Rhys

Reputation: 2877

How to add results of two select commands in same query

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?

Upvotes: 46

Views: 150039

Answers (5)

Pratik Roy
Pratik Roy

Reputation: 117

Repeat for Multiple aggregations like:

SELECT sum(AMOUNT) AS TOTAL_AMOUNT FROM ( 
    SELECT AMOUNT FROM table_1
    UNION ALL 
    SELECT AMOUNT FROM table_2 
    UNION ALL 
    SELECT ASSURED_SUM FROM table_3
)

Upvotes: 0

Saleh Ahmed
Saleh Ahmed

Reputation: 1

If you want to make multiple operation use 

select (sel1.s1+sel2+s2)

(select sum(hours) s1 from resource) sel1
join 
(select sum(hours) s2 from projects-time)sel2
on sel1.s1=sel2.s2

Upvotes: -2

Gordon Linoff
Gordon Linoff

Reputation: 1269583

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

Upvotes: 36

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656401

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

Upvotes: 10

John Woo
John Woo

Reputation: 263693

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.

Upvotes: 85

Related Questions