CODEWITHSUNDEEP
sqlselectnull
Tom
Tom

Reputation: 721

SQL Count empty fields

I'm not sure if this is possible or if it is, how to do it -

I have the following data in a database -

id     |    improve |   timeframe |  criteria |  impact
-------+------------+-------------+-----------+--------- 
1      |            |    Test     |   Test    |    Test
2      |    Test    |             |   Test    |   
3      |            |    Test     |           |    
-------+------------+-------------+-----------+---------

Ignoring the id column, how can I determine the number of fields out of the remaining 12 that are not null using an SQL query?

I have started with -

SELECT improve, timeframe, impact, criteria 
FROM data 
WHERE improve IS NOT NULL 
  AND timeframe IS NOT NULL 
  AND impact IS NOT NULL 
  AND criteria IS NOT NULL;

This only returns the number of rows, ie. 3.

Any ideas?

Thanks.

Upvotes: 1

Views: 46097

Answers (5)

user2710961
user2710961

Reputation: 69

SELECT id, COUNT(improve) + COUNT(timeframe) + COUNT(impact) + COUNT(criteria) FROM data GROUP BY id;

Upvotes: 0

Shawn Spencer
Shawn Spencer

Reputation: 1460

IF you're using SQL Server, use DATALENGTH().

SELECT improve, timeframe, impact, criteria 
FROM data 
WHERE DATALENGTH(improve) > 0 
AND DATALENGTH(timeframe) > 0 
AND DATALENGTH(impact) > 0 
AND DATALENGTH(criteria) >0;

DATALENGTH returns the length of the string in bytes, including trailing spaces. It sounded as though you're OK with blank fields, so DATALENGTH does the job. Otherwise, you could also use LEN(), which would trim any trailing space.

IF you are using MySQL, you can use CHARACTER_LENGTH, which removes trailing white space and then gives you a character count of the field you want to check.

Upvotes: -1

Michael
Michael

Reputation: 4090

SELECT Sum(case when improve is null then 0 else 1 end +
case when timeframe is null then 0 else 1 end +
case when impact is null then 0 else 1 end +
case when criteria is null then 0 else 1 end)
FROM data
group by improve, timeframe, impact, criteria

Upvotes: -2

brian chandley
brian chandley

Reputation: 1276

Something like this may get you going in the right direction

SELECT 
SUM(CASE WHEN improve IS NULL THEN 0 ELSE 1 END +
CASE WHEN timeframe IS NULL THEN  0 ELSE 1 END +
CASE WHEN criteria IS NULL THEN  0 ELSE 1 END +
CASE WHEN impact IS NULL THEN  0 ELSE 1 END)
from
data

Upvotes: 6

Cito
Cito

Reputation: 5613

SELECT count(improve) + count(timeframe) + count(impact) + count(criteria) FROM data

Upvotes: 6

Related Questions