SQL how to obtain two columns as a result of a two where condition in one statement

I’m enrolling in a Coursera course called SQL FOR DATA SCIENCE, offered by the University of California, Davis. I’m doing the last test and there is the next question based on a table of customer reviews:

Are there more reviews with the word “love” or with the word “hate” in them?

I’m getting the response doing two separate SELECT statements and obtaining two outputs:

SELECT COUNT(*) AS LOVE
FROM REVIEW
WHERE TEXT LIKE '%love%';

+------+
| LOVE |
+------+
| 1780 |
+------+
    
SELECT COUNT(*) AS HATE
FROM REVIEW
WHERE TEXT LIKE '%hate%';

+------+
| HATE |
+------+
|  232 |
+------+

Is there a way to obtain an output like this with a single statement:

+------+------+
| LOVE | HATE |
+------+------+
| 1780 |  232 |
+------+------+

I´m fairly new to SQL and though I got the response I would like to achieve in a better way. Any suggestion?

Answer

You can use conditional CASE..WHEN expression with aggregation :

SELECT SUM(CASE WHEN TEXT LIKE '%love%' THEN 1 END) AS LOVE,
       SUM(CASE WHEN TEXT LIKE '%hate%' THEN 1 END) AS HATE 
  FROM REVIEW

Demo

Advertisement

Answer