CODEWITHSUNDEEP
sqlgroup-bycountamazon-redshiftsql-null
bz_2020
bz_2020

Reputation: 79

Subtraction of two SELECT statements in SQL (redshift)

Can someone explain why the below doesn't work? 

((SELECT COUNT(*) FROM Table1) - (SELECT Count(Metric) FROM Table1)) as X

Count(*) will give me all the rows in the table and Count(Metric) will give me the non-null values in the Metric column. So the difference between these will give me the number of null values in the Metric column and I have labelled this column X. I just want the difference between the two in Column X but not sure why it isn't working.

By the way, I know I can get it to work via the below:

SELECT COUNT(*) as a, count(metric) as b, COUNT(*)-COUNT(metric) as c

Upvotes: 0

Views: 2853

Answers (3)

Christoph
Christoph

Reputation: 3642

Not sure about amazon-redshift, but in standard SQL I would just count the records where the field is null instead of counting all minus where they are not null.

SELECT COUNT(*) FROM Table1 WHERE Metric IS NULL;

Upvotes: 0

GMB
GMB

Reputation: 222492

You would need to select the result:

SELECT ((SELECT COUNT(*) FROM Table1) - (SELECT Count(Metric) FROM Table1)) as X

But it is simpler to use conditional aggregation:

SELECT SUM(CASE WHEN Metrics IS NULL THEN 1 ELSE 0 END) X FROM table1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

A SELECT query needs to start with SELECT (or WITH or a parenthesis if the query is a compound query with a set operator such as UNION ALL).

One method is:

SELECT ((SELECT COUNT(*) FROM Table1) - (SELECT Count(Metric) FROM Table1)) as X

A better method is:

SELECT COUNT(*) - Count(Metric) as X
FROM Table1

Upvotes: 0

Related Questions