CODEWITHSUNDEEP
sqlunionsnowflake-cloud-data-platform
Chris
Chris

Reputation: 45

Snowflake can't union two queries with same amount of columns and datatypes

Trying to combine results which are the following results on snowflake. I used SQL server before this and im confused that Snowflake can't use UNION statements? Has anyone with lots of Snowflake experience have a solution to this problem?

SELECT
    PARSE_JSON(extra[0]['party'])[0]['trait1'] AS Trait1,
    COUNT(Trait1) AS T1
FROM E_OT AS EOT
WHERE Trait1 != 'NULL'
GROUP BY Trait1
ORDER BY T1 DESC
 
UNION
 
SELECT
    PARSE_JSON(extra[0]['party'])[0]['trait2'] AS Trait2,
    COUNT(Trait2) AS T2
FROM E_OT
WHERE Trait2 != 'NULL'
GROUP BY Trait2
ORDER BY T2 DESC

enter image description here

Upvotes: 0

Views: 4369

Answers (3)

Vivek Pant
Vivek Pant

Reputation: 21

Try this:

SELECT TRAIT1, T1 FROM ( SELECT PARSE_JSON(EXTRA[0]['PARTY'])[0]['TRAIT1'] AS TRAIT1, COUNT(TRAIT1) AS T1 FROM E_OT AS EOT WHERE TRAIT1 != 'NULL' GROUP BY TRAIT1 ORDER BY T1 DESC )

UNION

SELECT TRAIT2, T2 FROM ( SELECT PARSE_JSON(EXTRA[0]['PARTY'])[0]['TRAIT2'] AS TRAIT2, COUNT(TRAIT2) AS T2 FROM E_OT WHERE TRAIT2 != 'NULL' GROUP BY TRAIT2 ORDER BY T2 DESC )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think that what you are trying to do is simpler using grouping sets:

SELECT COALESCE(PARSE_JSON(extra[0]['party'])[0]['trait1'],
                PARSE_JSON(extra[0]['party'])[0]['trait2']
               ) AS Trait,
       COUNT(*) AS T1
FROM E_OT AS EOT
GROUP BY GROUPING SETS ( (PARSE_JSON(extra[0]['party'])[0]['trait1']),
                         (PARSE_JSON(extra[0]['party'])[0]['trait2'])
                       )   
ORDER BY T1 DESC;

I'm not sure what your WHERE clause is doing. It should be returning an error unless there is a TRAIT1 column in the data.

Upvotes: 0

Gokhan Atil
Gokhan Atil

Reputation: 10079

The ORDER BY clause should be at the end:

SELECT
    PARSE_JSON(extra[0]['party'])[0]['trait1'] AS Trait1,
    COUNT(Trait1) AS T1
FROM E_OT AS EOT
WHERE Trait1 != 'NULL'
GROUP BY Trait1
UNION
SELECT
    PARSE_JSON(extra[0]['party'])[0]['trait2'] AS Trait2,
    COUNT(Trait2) AS T2
FROM E_OT
WHERE Trait2 != 'NULL'
GROUP BY Trait2
ORDER BY T1 DESC

https://docs.snowflake.com/en/sql-reference/operators-query.html#general-syntax

Upvotes: 2

Related Questions