Stack Exchange
How to merge two SELECT queries with different WHERE and GROUP BY clauses
I have two queries as follows:
SELECT 
    Actor2Code, 
    COUNT(Actor2Code) AS playermentions 
FROM [events] 
WHERE 
    Year=" + Date + " 
    AND 
    (
        (
            (Actor1CountryCode ='" + mainPlayer1 + "') 
            AND 
            (Actor2CountryCode != '" + mainPlayer1 + "')
        ) 
        OR 
        (
            Actor1CountryCode='" + mainPlayer2 + "' 
            AND 
            Actor2CountryCode !='" + mainPlayer2 + "'
        )
    ) 
GROUP BY Actor2Code;

AND
SELECT 
    Actor1Code, 
    COUNT(Actor1Code) AS playermentions 
FROM [events] 
WHERE 
    Year=" + Date + " 
    AND 
    (
        (
            (Actor2CountryCode ='" + mainPlayer1 + "') 
            AND 
            (Actor1CountryCode != '" + mainPlayer1 + "')
        ) 
        OR 
        (
            Actor2CountryCode='" + mainPlayer2 + "' 
            AND 
            Actor1CountryCode !='" + mainPlayer2 + "'
        )
    ) 
GROUP BY Actor1Code

I merge the result in c# and I have a list of actors and another list of number of mentions.How can I make these two queries into one so that I have one actor list and no duplicated actorcodes? Some actors are in the response of both queries with different number of mentions. I want to write a query that merges the result of these two queries and gives me a list including distinct actor1codes and actor2codes and the number of mentions (for the duplicated values I need to have the sum of number of mentions)

Have you attempted it yet>


and then group the result of the union on ActorCode.


I initially thought UNION but that may not work based on what he is looking for

Is this a SELECT or can this be a PROCEDURE in SQL

It can't be a procedure I believe, because I am reading from Google Big query and I can send only one query at a time

This almost works, but you have to manually remove duplicates.
SELECT ActorCode, playermentions FROM

(SELECT 
    Actor2Code as ActorCode, 
    COUNT(Actor2Code) AS playermentions 
FROM [events] 
WHERE 
    Year=" + Date + " 
    AND 
    (
        (
            (Actor1CountryCode ='" + mainPlayer1 + "') 
            AND 
            (Actor2CountryCode != '" + mainPlayer1 + "')
        ) 
        OR 
        (
            Actor1CountryCode='" + mainPlayer2 + "' 
            AND 
            Actor2CountryCode !='" + mainPlayer2 + "'
        )
    ) 
GROUP BY Actor2Code),
(SELECT 
    Actor1Code as ActorCode, 
    COUNT(Actor1Code) AS playermentions 
FROM [events] 
WHERE 
    Year=" + Date + " 
    AND 
    (
        (
            (Actor2CountryCode ='" + mainPlayer1 + "') 
            AND 
            (Actor1CountryCode != '" + mainPlayer1 + "')
        ) 
        OR 
        (
            Actor2CountryCode='" + mainPlayer2 + "' 
            AND 
            Actor1CountryCode !='" + mainPlayer2 + "'
        )
    ) 
GROUP BY Actor1Code);


Thanks for your response. Your code produces the following error:Error: Expression 'ActorCode' is not present in the GROUP BY list

I can't find any docs on this, but try using GROUP BY ActorCode instead of GROUP BY Actor2Code.

GROUP BY ActorCode worked, but now the result is as if I have merged the two arrays that I got from the two queries separately.Merged both ActorCodes and NumberOfMentions. My problem is that I want distinct ActorCodes and number of mentions should be the sum of number of mentions for duplicate values.

Sum huh? Maybe wrap it in an outer group by?
