CODEWITHSUNDEEP
sqlsql-server
Jonathan
Jonathan

Reputation: 172

SQL- count the non NULL values and count the rows that has string "1"

I'm trying to count non null row in a column but it's counting all the rows and and count the rows in a column that has string "1". I was able to count the rows in a column that has string "1" for the 1st column but on the 2nd one, it's count the "0" too. I've seen some articles here but it didn't resolved the issue.

SELECT NAME as Agent_Name, COUNT(case when Thumbs_Up= 1 then 1 else null end) as Thumbs_Up, 
   COUNT(case when No_Solution_Found =1 then 1 else null end) as No_Solution,
   COUNT(case when Save is null then 0 else 1 end) as Total_Saves,
 FROM table 
 GROUP BY NAME

Table:

Name      | Thumbs_up | No_Solution_Found | Save
Jonathan  | 1         | 0                 | Saved  
Mike      | 0         | 1                 | Null    
Peter     | 1         | 0                 | Null  
Mike      | 1         | 0                 | Saved
Peter     | 0         | 1                 | Saved
Mike      | 1         | 0                 | Saved
Peter     | 0         | 1                 | Saved

Expected results:

Name      | Thumbs_up | No_Solution       | Total_Save
Jonathan  | 1         | 0                 | 1
Mike      | 2         | 1                 | 2
Peter     | 1         | 2                 | 2

Upvotes: 1

Views: 3306

Answers (3)

IShubh
IShubh

Reputation: 364

Try the following query-:

Select 
Name,
sum(Thumbs_up),
sum(No_Solution_Found),
count(case when [Save] is not null then 1 else null end) as Total_save 
from TABLE 
group by Name

SQL Server 2014

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Since only the Save column has NULLs, I assume that's the column you have the problem with.

In your query you wrote:

COUNT(case when Save is null then 0 else 1 end) as Total_Saves,

That is, you're replacing NULL by 0, which is a non null value and therefore is counted.

You presumable wanted to just write:

COUNT(Save) as Total_Saves

(And BTW, there is a comma after as Total_Saves in your query, that doesn't belong there, as no other column expression follows.)

Upvotes: 2

flyingfox
flyingfox

Reputation: 13506

Try with SUM instead of COUNT

SELECT NAME as Agent_Name, 
   SUM(case when Thumbs_Up = 1 then 1 else 0 end) as Thumbs_Up, 
   SUM(case when No_Solution_Found =1 then 1 else 0 end) as No_Solution,
   SUM(case when Save is null then 0 else 1 end) as Total_Saves,
 FROM table 
 GROUP BY NAME

Upvotes: 4

Related Questions