How to merge two SELECT queries with different WHERE and GROUP BY clauses

Question

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)

I initially thought UNION but that may not work based on what he is looking for — logixologist, Dec 08 '14 at 18:41
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 — nasim, Dec 08 '14 at 18:49

score 0 · Accepted Answer · answered Dec 08 '14 at 19:44

0

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);

answered Dec 08 '14 at 19:44

Jonathan Allen

68,373
70
259
447

Thanks for your response. Your code produces the following error:Error: Expression 'ActorCode' is not present in the GROUP BY list – nasim Dec 08 '14 at 20:27
I can't find any docs on this, but try using `GROUP BY ActorCode` instead of `GROUP BY Actor2Code`. – Jonathan Allen Dec 08 '14 at 20:32
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. – nasim Dec 08 '14 at 20:43
Sum huh? Maybe wrap it in an outer group by? – Jonathan Allen Dec 09 '14 at 05:41

How to merge two SELECT queries with different WHERE and GROUP BY clauses

1 Answers1