CODEWITHSUNDEEP
sql-server
abs786123
abs786123

Reputation: 609

Summing two SQL queries into one

I have the below query which I am trying to sum, at the minute, I am getting two lines, one with each sum of each query, is there anyway to add the two sums so one row with the total sum is returned of both queries? Thank you in advance

 SELECT count(*)

    FROM
      eUser  eUser2 RIGHT OUTER JOIN prcIncidentManagement ON (eUser2.eUserName=prcIncidentManagement.AssignedTo)
       INNER JOIN eFolder ON (prcIncidentManagement.EFOLDERID=eFolder.eFolderID)
    WHERE
     eFolder.eCreationTime  between '01/01/2015'  and '13/10/2015'
    union
    SELECT count(*)
    FROM
      eUser RIGHT OUTER JOIN prcRequestFulfilment ON (prcRequestFulfilment.AssignedTo=eUser.eUserName)
       INNER JOIN eFolder ON (eFolder.eFolderID=prcRequestFulfilment.EFOLDERID)
    WHERE
     eFolder.eCreationTime  between '01/01/2015'  and '13/10/2015'

Upvotes: 0

Views: 55

Answers (4)

Paritosh
Paritosh

Reputation: 2379

Well you can take the both output and then sum. No need of Using Union all Or Union.

Declare @SumOfIncident int= (SELECT count(*)

FROM eUser eUser2 RIGHT OUTER JOIN prcIncidentManagement ON (eUser2.eUserName=prcIncidentManagement.AssignedTo) INNER JOIN eFolder ON (prcIncidentManagement.EFOLDERID=eFolder.eFolderID)

WHERE eFolder.eCreationTime between '01/01/2015' and '13/10/2015' )


Declare @requestOfFullfillment int= (SELECT count(*)

FROM eUser eUser2 RIGHT OUTER JOIN prcIncidentManagement ON (eUser2.eUserName=prcIncidentManagement.AssignedTo) INNER JOIN eFolder ON (prcIncidentManagement.EFOLDERID=eFolder.eFolderID)

WHERE eFolder.eCreationTime between '01/01/2015' and '13/10/2015' )

SELECT @SumOfIncident   + @requestOfFullfillment

Upvotes: 0

Praveen
Praveen

Reputation: 9335

Try:

select sum(cnt) total_cnt
from (
    SELECT count(*) cnt
    FROM eUser  eUser2 
    RIGHT OUTER JOIN prcIncidentManagement ON eUser2.eUserName = prcIncidentManagement.AssignedTo
    INNER JOIN eFolder ON prcIncidentManagement.EFOLDERID = eFolder.eFolderID
    WHERE eFolder.eCreationTime  between '01/01/2015'  and '10/13/2015'

    union all

    SELECT count(*)
    FROM eUser 
    RIGHT OUTER JOIN prcRequestFulfilment ON prcRequestFulfilment.AssignedTo = eUser.eUserName
    INNER JOIN eFolder ON eFolder.eFolderID = prcRequestFulfilment.EFOLDERID
    WHERE eFolder.eCreationTime  between '01/01/2015'  and '10/13/2015'
) x

You can use union all instead of union.
If you use union and the count returned by the first and second query are same then, union will remove one count as it allow only distinct values.
So, you wont get the result you expecting.

Date must in the form mm/dd/yyyy else use convert to get the data from string.

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

This should do it for you, except I recommend you use UNION ALL instead of UNION.

If both your queries return the same value, then the UNION operator will make a implicit DISTINCT between the two values and your SUM will just be one of those values, instead of being doubled.

SELECT SUM(cnt_val) Sum_Values
FROM (
    SELECT count(*) cnt_val
    FROM eUser eUser2
    RIGHT JOIN prcIncidentManagement
        ON (eUser2.eUserName = prcIncidentManagement.AssignedTo)
    INNER JOIN eFolder
        ON (prcIncidentManagement.EFOLDERID = eFolder.eFolderID)
    WHERE eFolder.eCreationTime BETWEEN '01/01/2015'
            AND '13/10/2015'

    UNION ALL

    SELECT count(*) cnt_val
    FROM eUser
    RIGHT JOIN prcRequestFulfilment
        ON (prcRequestFulfilment.AssignedTo = eUser.eUserName)
    INNER JOIN eFolder
        ON (eFolder.eFolderID = prcRequestFulfilment.EFOLDERID)
    WHERE eFolder.eCreationTime BETWEEN '01/01/2015'
            AND '13/10/2015'
    ) Result_Set

So, assuming the results of the two queries are:

2 --> first query result
2 --> second query result

Then the total sum will be 2, if you're using UNION instead of UNION ALL.

Upvotes: 1

Abhishek
Abhishek

Reputation: 2490

This should work - 

SELECT SUM(Cnt) FROM
    (SELECT count(*) AS Cnt

        FROM
          eUser  eUser2 RIGHT OUTER JOIN prcIncidentManagement ON (eUser2.eUserName=prcIncidentManagement.AssignedTo)
           INNER JOIN eFolder ON (prcIncidentManagement.EFOLDERID=eFolder.eFolderID)
        WHERE
         eFolder.eCreationTime  between '01/01/2015'  and '13/10/2015'
        union
        SELECT count(*)
        FROM
          eUser RIGHT OUTER JOIN prcRequestFulfilment ON (prcRequestFulfilment.AssignedTo=eUser.eUserName)
           INNER JOIN eFolder ON (eFolder.eFolderID=prcRequestFulfilment.EFOLDERID)
        WHERE
         eFolder.eCreationTime  between '01/01/2015'  and '13/10/2015') A

Upvotes: 0

Related Questions