Query with multiple distinct in a column

Question

I am trying to write a query on a column in a table. In column "text" I can have values of either 5 or 10 or null. I want to get count of number of rows in the table for 5 or 10 or null. I wrote following query it is not working properly

select COUNT(*) from t_test
select COUNT(text) from t_test where text=5
select COUNT(text) from t_test where text=10
select COUNT(text) from t_test where text=null

I can get first three select statements values but the last one with null returns zero while there are rows with null in it. How to write this query? Thanks

You want text IS null

bsoist
– bsoist

2013-07-08 01:16:38 +00:00
Commented Jul 8, 2013 at 1:16 — bsoist
– bsoist, Commented Jul 8, 2013 at 1:16

Gordon Linoff · Accepted Answer · 2013-07-08 01:14:29Z

3

You should just use conditional summation:

select count(*),
       sum(case when text = '5' then 1 else 0 end) as Num_5,
       sum(case when text = '10' then 1 else 0 end) as Num_10,
       sum(case when text is null then 1 else 0 end) as Num_Null
from t_test;

This is assuming that a field called text is stored as a character string, so the constants are put in quotes. If it is really a number, first I'd be curious why it is called text. In that case, you can dispense with the single quotes.

In your case, the last one doesn't work because count(text) counts non-null values. But the where clause only keeps NULL values. For that one, you should use count(*). The correct query would be:

select count(*) from t_test where text is null;

answered Jul 8, 2013 at 1:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

STLDev · Accepted Answer · 2013-07-08 01:22:26Z

2

What you need for that final query is:

select COUNT(*) from t_test where text is null

Notice:

COUNT(*) rather than COUNT(TEXT) which is null, rendering no values
is null rather than =null

Your final set of queries is thus:

select COUNT(*) from t_test
select COUNT(text) from t_test where text=5
select COUNT(text) from t_test where text=10
select COUNT(*) from t_test where text is null

edited Jul 8, 2013 at 1:22

answered Jul 8, 2013 at 1:15

STLDev

6,17829 silver badges37 bronze badges

Collectives™ on Stack Overflow

Query with multiple distinct in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related