CODEWITHSUNDEEP
db2
Travis Heeter
Travis Heeter

Reputation: 14054

How to combine the results of different queries into one?

I have the results of a sentiment analysis in one column. I'd like to show what each number represents and the count, like this:

 Neg | Neutral | Pos 
---------+-----+-----
  30 |   55    | 100

I've tried many different approaches I've found on the internet, to no avail. It seems the db2 syntax changes frequently, and all the documentation I've found does not work on my current bluemix account.

The closest I can get is with UNION:

SELECT  COUNT(*) as NEGATIVE      FROM C7  WHERE SENTIMENT = 1
union
SELECT  COUNT(*) as NEUTRAL       FROM C7  WHERE SENTIMENT = 2
union
SELECT  COUNT(*) as Pos           FROM C7  WHERE SENTIMENT = 3

However, this gives me:

NEGATIVE
--------
   30
   55
  100

How can I combine the results of each query side-by-side?

I tried the answers from the post Pivoting in DB2, but I got errors:

SELECT 
  C7.SENTIMENT,
  COUNT(CASE WHEN C7.SENTIMENT = 1 THEN Value END) AS NEGATIVE,
  COUNT(CASE WHEN C7.SENTIMENT = 2 THEN Value END) AS NEUTRAL,
  COUNT(CASE WHEN C7.SENTIMENT = 3 THEN Value END) AS POSITIVE,
FROM C7
GROUP BY C7.SENTIMENT

SQL0206N: SQL0206N "VALUE" is not valid in the context where it is used. SQLSTATE=42703

SELECT DISTINCT
  C7.SENTIMENT,
    (SELECT value  FROM C7  WHERE C7.SENTIMENT = 1) AS VERY_NEGATIVE,
    (SELECT value  FROM C7  WHERE C7.SENTIMENT = 2) AS NEGATIVE,
    (SELECT value  FROM C7  WHERE C7.SENTIMENT = 3) AS NEUTRAL
FROM C7

SQL0206N: SQL0206N "VALUE" is not valid in the context where it is used. SQLSTATE=42703

Upvotes: 0

Views: 60

Answers (1)

Travis Heeter
Travis Heeter

Reputation: 14054

OK, so after reworking a few answers, I finally got what I was looking for:

SELECT DISTINCT
  (SELECT COUNT(SENTIMENT) FROM C7 WHERE C7.SENTIMENT = 1) AS NEGATIVE,
  (SELECT COUNT(SENTIMENT) FROM C7 WHERE C7.SENTIMENT = 2) AS NEUTRAL,
  (SELECT COUNT(SENTIMENT) FROM C7 WHERE C7.SENTIMENT = 3) AS POSITIVE
FROM C7

This seems like something I hovered around for a long time, but couldn't find because some posts answer the question with bizarre table names, and values.

So basically, to get queries next to each other, you have to use multiple SELECTs.

Also, notice I used DISTINCT because otherwise it would have given a copy of the same data for every row in my database.

Upvotes: 1

Related Questions