how to count two similiar Sql select and add together?-CodePudding

I have two query and I want to add their result together. Basically i want to count all occurrences then add those ones to it where it has a connection with another table. So if it appears in the other table 2 times the final count value for the notRelevantId should be 3.

SELECT table1.notRelevantId, COUNT(0) as numberOfOccurrences
FROM table1
WHERE Deleted = 0 (AND other filters)
GROUP BY table1.notRelevantId

SELECT table1.notRelevantId, COUNT(0) as numberOfOccurrences
FROM table1
INNER JOIN table2 ON table1.connectionId = table2.connectionId
WHERE Deleted = 0 (AND other filters) AND table2.connectionId is not null
GROUP BY table1.notRelevantId

query result

notRelevantId	numberOfOccurrences
464	15
536	1
528	358
465	254

query result

notRelevantId	numberOfOccurrences
464	14
528	352
465	166

Expected result:

notRelevantId	numberOfOccurrences
464	29
536	1
528	710
465	420

CodePudding user response：

UNION ALL to put them together, then SUM by notRelevantId

SELECT notRelevantId, SUM(numberOfOccurrences) AS numberOfOccurrences
FROM (
SELECT table1.notRelevantId, COUNT(*) as numberOfOccurrences
FROM table1
WHERE Deleted = 0 (AND other filters)
GROUP BY table1.notRelevantId
UNION ALL 
SELECT table1.notRelevantId, COUNT(*) as numberOfOccurrences
FROM table1
INNER JOIN table2 ON table1.connectionId = table2.connectionId
WHERE Deleted = 0 (AND other filters) AND table2.connectionId is not null
GROUP BY table1.notRelevantId
)
GROUP BY notRelevantId;

CodePudding user response：

There are cleaner (easier to read) ways to write this but...

SELECT 
    NoConnect.NotRelevantID
    ,NoConnect.nubmerOfOccurrences   WithConnect.numberOfOccurrences AS [countOccurrences]
FROM 
    (
        SELECT table1.notRelevantId, COUNT(0) as numberOfOccurrences
        FROM table1
        WHERE Deleted = 0 (AND other filters)
        GROUP BY table1.notRelevantId
    ) AS NoConnect
  JOIN
    (
        SELECT table1.notRelevantId, COUNT(0) as numberOfOccurrences
        FROM table1
          INNER JOIN table2 ON table1.connectionId = table2.connectionId
        WHERE Deleted = 0 (AND other filters) AND table2.connectionId is not null
        GROUP BY table1.notRelevantId
    ) AS WithConnect ON WithConnect.NotRelevantID = NoConnect.NotRelevantID

CodePudding user response：

I think you can simply use this query, incase the first query has all required records. Otherwise you can use UNION and SUM

SELECT A.notRelevantId,SUM(numberOfOccurrences) 
FROM 
( SELECT table1.notRelevantId, COUNT(0) as numberOfOccurrences
FROM table1
WHERE Deleted = 0 (AND other filters)
GROUP BY table1.notRelevantId ) A,
( SELECT table1.notRelevantId, COUNT(0) as numberOfOccurrences
FROM table1
INNER JOIN table2 ON table1.connectionId = table2.connectionId
WHERE Deleted = 0 (AND other filters) AND table2.connectionId is not null
GROUP BY table1.notRelevantId ) B 
WHERE 
A.notRelevantId = B.notRelevantId( ) GROUP BY A.notRelevantId