Add the results of two tables

I have two queries, they give me a TOTAL number. I need to add the total of both queries and give me a TOTALSUM from both, the queries are below. Help is appreciated

1. QUERY # 1

SELECT  COUNT(Cases.Id) AS TOTAL
FROM    Cases
        LEFT JOIN Users ON AlienId = UserId
        INNER JOIN Processcatalog ON Process = ProcesscatalogID
        INNER JOIN Activities ON Activities.CaseId = Cases.Id
        INNER JOIN Atts AS b ON Cases.Id = b.CaseId
        LEFT JOIN Users AS e ON e.UserId = Cases.EmpId
WHERE   Activities.ActType = 'HISTORY'
        AND Cases.FirmId = 2
        AND ( DATEADD(DAY, Activities.responsible, Activities.LastModified) < GETDATE() )
        AND b.UserId = 1713
        AND Activities.DateInitiated IS NULL
        AND Cases.Archived = 0;
            
2. QUERY # 2
            
SELECT  COUNT(*) AS TOTALCASES
FROM    Activities a
        INNER JOIN Cases AS b ON a.CaseId = b.Id
        INNER JOIN Atts AS d ON d.CaseId = b.Id
        INNER JOIN Users AS c ON a.responsible = c.UserId
        INNER JOIN Users AS e ON b.AlienId = e.UserId
WHERE   a.FirmId = 2
        AND ( a.ActType = 'TASK'
              OR a.ActType = 'DEADLINE'
            )
        AND a.Complete = '0'
        AND d.UserId = 1713
        AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
        AND b.Archived <> 1;

We need to add total from query one + total from query 2 to get totalsum
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
UNION ALL them together, throw it in a subquery, and sum
SELECT SUM(a.total)
FROM (
   --- Query 1
  SELECT  COUNT(Cases.Id) AS total
  FROM    Cases blah blah blah blah
  UNION ALL
   -- Query 2
 SELECT  COUNT(*) -- no alias needed here, already defined in query 1
 FROM    Activities blah blah blah) a

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
OR, if you want to use those totals later in your SP, then assign variables to the SUM's..

Declare @total numeric(19,4), @TotalCases numeric(19,4)

SELECT @total = COUNT(Cases.Id) AS total FROM --- rest of query 1 goes here
SELECT @TotalCases = COUNT(*) FROM -- rest of query 2 goes here

SELECT @total as 'query 1 total', @TotalCases as 'query 2 total', @total + @TotalCases as 'both total'

Open in new window

AleksAuthor Commented:
This is my query and I get an error:

---

  SELECT  COUNT(Cases.Id) AS total
  FROM    Activities a
        INNER JOIN Cases AS b ON a.CaseId = b.Id
        INNER JOIN Atts AS d ON d.CaseId = b.Id
        INNER JOIN Users AS c ON a.responsible = c.UserId
        INNER JOIN Users AS e ON b.AlienId = e.UserId
WHERE   a.FirmId = 2
        AND ( a.ActType = 'TASK'
              OR a.ActType = 'DEADLINE'
            )
        AND a.Complete = '0'
        AND d.UserId = 1713
        AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
        AND b.Archived <> 1
  UNION ALL
   -- Query 2
 SELECT  COUNT(*) -- no alias needed here, already defined in query 1
 FROM    Cases
        LEFT JOIN Users ON AlienId = UserId
        INNER JOIN Processcatalog ON Process = ProcesscatalogID
        INNER JOIN Activities ON Activities.CaseId = Cases.Id
        INNER JOIN Atts AS b ON Cases.Id = b.CaseId
        LEFT JOIN Users AS e ON e.UserId = Cases.EmpId
WHERE   Activities.ActType = 'HISTORY'
        AND Cases.FirmId = 2
        AND ( DATEADD(DAY, Activities.responsible, Activities.LastModified) < GETDATE() )
        AND b.UserId = 1713
        AND Activities.DateInitiated IS NULL
        AND Cases.Archived = 0

---

Error :

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '0'.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

AleksAuthor Commented:
Apologize, error is:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Cases.Id" could not be bound.
AleksAuthor Commented:
I fixed it, it now reads as below. But it returns two values, it doesn't add them.

I get :  21 and 89
I should get : 110

--

SELECT  COUNT(b.Id) AS total
   FROM    Activities a
         INNER JOIN Cases AS b ON a.CaseId = b.Id
         INNER JOIN Atts AS d ON d.CaseId = b.Id
         INNER JOIN Users AS c ON a.responsible = c.UserId
         INNER JOIN Users AS e ON b.AlienId = e.UserId
 WHERE   a.FirmId = 2
         AND ( a.ActType = 'TASK'
               OR a.ActType = 'DEADLINE'
             )
         AND a.Complete = '0'
         AND d.UserId = 1713
         AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
         AND b.Archived <> 1
   UNION ALL
   
  SELECT  COUNT(Cases.id)
  FROM    Cases
         LEFT JOIN Users ON AlienId = UserId
         INNER JOIN Processcatalog ON Process = ProcesscatalogID
         INNER JOIN Activities ON Activities.CaseId = Cases.Id
         INNER JOIN Atts AS b ON Cases.Id = b.CaseId
         LEFT JOIN Users AS e ON e.UserId = Cases.EmpId
 WHERE   Activities.ActType = 'HISTORY'
         AND Cases.FirmId = 2
         AND ( DATEADD(DAY, Activities.responsible, Activities.LastModified) < GETDATE() )
         AND b.UserId = 1713
         AND Activities.DateInitiated IS NULL
         AND Cases.Archived = 0
Jim HornMicrosoft SQL Server Data DudeCommented:
In the above query you would get two rows, 21 and 89.
Note in my original comment that I threw all of that into a subquery, and SUM on that.

SELECT SUM(a.total)
FROM (
 --- what you have above
   ) a

Open in new window

AleksAuthor Commented:
Sorry but I must be missing something. I did that and got a syntax error:

----

SELECT SUM(a.total)
FROM (

SELECT  COUNT(b.Id) AS total
   FROM    Activities a
         INNER JOIN Cases AS b ON a.CaseId = b.Id
         INNER JOIN Atts AS d ON d.CaseId = b.Id
         INNER JOIN Users AS c ON a.responsible = c.UserId
         INNER JOIN Users AS e ON b.AlienId = e.UserId
 WHERE   a.FirmId = 2
         AND ( a.ActType = 'TASK'
               OR a.ActType = 'DEADLINE'
             )
         AND a.Complete = '0'
         AND d.UserId = 1713
         AND a.DateInitiated < LEFT(DATEADD(DAY, 0, GETDATE()), 12)
         AND b.Archived <> 1
         
   UNION ALL
   
  SELECT  COUNT(Cases.id)
  FROM    Cases
         LEFT JOIN Users ON AlienId = UserId
         INNER JOIN Processcatalog ON Process = ProcesscatalogID
         INNER JOIN Activities ON Activities.CaseId = Cases.Id
         INNER JOIN Atts AS b ON Cases.Id = b.CaseId
         LEFT JOIN Users AS e ON e.UserId = Cases.EmpId
 WHERE   Activities.ActType = 'HISTORY'
         AND Cases.FirmId = 2
         AND ( DATEADD(DAY, Activities.responsible, Activities.LastModified) < GETDATE() )
         AND b.UserId = 1713
         AND Activities.DateInitiated IS NULL
         AND Cases.Archived = 0
         
         )

----

Even without the parenthesis I get an error.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ')'.


If I run what is inside the parenthesis no errors.
Jim HornMicrosoft SQL Server Data DudeCommented:
>Msg 102, Level 15, State 1, Line 35
>Incorrect syntax near ')'.
Do us a favor and when you get an error in T-SQL click on the line, note the cursor will jump to the line that throws the error, then tell us what that line is.  No idea what line 35 is looking at the above.
Jim HornMicrosoft SQL Server Data DudeCommented:
>        )
forgot the a, should be ) a.
SQL requires that subqueries have a name, even if it's the only set in a SELECT query.  Kinda silly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
gotcha. works now thanks !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.