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

how to count null values in sql server
sql count not null columns
sql count null values in a row
count number of rows in sql query result
sql count null as 0
sql count null values in all columns
how to count null values in mysql
mysql count not null

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

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

SQL Tip: COUNTing NULL values – Benjamin's Blog, The COUNT function can tell you the total number of rows returned in a result set (both NULL and non-NULL together depending on how it's used). For example: Using SELECT COUNT(*) or SELECT COUNT(1) (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values. COUNT() function. The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows. Syntax: COUNT(*) COUNT( [ALL|DISTINCT] expression ) The above syntax is the general SQL 2003 ANSI standard syntax.

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.)

Getting the Count of Non-Null Values in a SQL Server Table with , Getting the Count of Non-Null Values in a SQL Server Table with PowerShell Many of these columns have countless nulls, which reduces our ability to Next, we'll populate a data point in each row in only one column of our Function Execute-Sql { Param( [Parameter(Mandatory=$true)][string]$server  The SQL COUNT function is an aggregate function that returns the number of rows returned by a query. You can use the COUNT function in the SELECT statement to get the number of employees, the number of employees in each department, the number of employees who hold a specific job, etc. The following illustrates the syntax of the SQL COUNT function: COUNT([ALL | DISTINCT] expression);

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

COUNT Function, The notation COUNT(*) includes NULL values in the total. The notation COUNT( column_name ) only considers rows where the column contains a non- NULL value. the general-purpose MAX() and MIN() values are computed from the string  The COUNT() function has another form as follows: COUNT(*) In this form, the COUNT(*) returns the number of rows in a specified table. COUNT(*) does not support DISTINCT and takes no parameters. It counts each row separately and includes rows that contain NULL values. In summary: COUNT(*) counts the number of items in a set

SQL Server COUNT Function By Practical Examples, The COUNT() function has another form as follows: COUNT(*). In this form COUNT(*) does not support DISTINCT and takes no parameters. It counts each row separately and includes rows that contain NULL values. In summary: COUNT​(*)  The SQL COUNT(), AVG() and SUM() Functions. The COUNT() function returns the number of rows that matches a specified criteria. The AVG() function returns the average value of a numeric column. The SUM() function returns the total sum of a numeric column. SELECT COUNT(column_name) FROM table_name.

COUNT, COUNT - An aggregate function that returns the number of rows in a table or a It counts each row separately, including rows that contain NULL values. The following examples are not valid when Title is a string field and Notes and has table-level SELECT privilege by invoking the $SYSTEM.SQL.CheckPriv() method​. In this post: * SQL count null and not null values for several columns * MySQL select count null values per column * Count by multiple selects * MySQL count values for every table and schema * Oracle SQL select count null values per column * Count by multiple selects * Count by single select query * Oracle count null and not null values for several columns If you need to check the number of

SQLite COUNT Function: Count Items In A Group, This tutorial shows you how to use SQLite COUNT function to count number of items in a group. ALL : when you specify all, the COUNT() function counts all non-null values include duplicates. The COUNT(*) function returns the number of rows in a table, including the First, create a table called t1 that has one column:. To count NULL values only. In case you want to get the count of all NULL values only, you can try this COUNT(*) – COUNT(ColA) instead of COUNT(ColA) i.e. just subtract the count of total NOT NULL values from count of total values. COUNT(*) counts all rows even it has NULL in all the columns. So what you suggest?