CODEWITHSUNDEEP
mysqlsql
user2341596
user2341596

Reputation: 65

How to Select count() inside select statement

After hours of trying, i have finally come to terms i need assistance.

I'm trying to select a row from TableA and then count the number of cells of that row that are not empty.

I know this is faulty but it communicates my intention

SELECT COUNT(colName), 
    (SELECT (column1, column2, column3, column4) AS colName 
     FROM TableA 
     WHERE location= location) 
AS colCount
FROM TableA  
WHERE colName IS NOT NULL

Upvotes: 0

Views: 80

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SQL DEMO

SELECT id, 
       (`column1` IS NOT NULL) + 
       (`column2` IS NOT NULL) + 
       (`column3` IS NOT NULL) as notnull
FROM Table1

Upvotes: 1

DanB
DanB

Reputation: 2124

select location,
    sum(case when column1 is not null then 1 else 0 end) +
    sum(case when column2 is not null then 1 else 0 end) +
    sum(case when column3 is not null then 1 else 0 end) +
    sum(case when column4 is not null then 1 else 0 end) cnt
from TableA
group by location

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

There may be a slicker way but a brute-force way would be:

SELECT
   location, 
    CASE WHEN column1 IS NULL THEN 0 ELSE 1 END + 
    CASE WHEN column2 IS NULL THEN 0 ELSE 1 END + 
    CASE WHEN column3 IS NULL THEN 0 ELSE 1 END + 
    CASE WHEN column4 IS NULL THEN 0 ELSE 1 END
   AS colCount 
 FROM TableA

Upvotes: 1

Related Questions