<div>
Unless there's something better in SQL 2012, the standard practice is to take (the length of the field) minus (the length of the field without that character). Something like: 
 

<pre><code class="code-1 nolinks" id="code-20-42710037-1">SELECT LEN(CAST([YourField] AS VARCHAR(2000)) - LEN(REPLACE(CAST([YourField] AS VARCHAR(2000), '0', ''))
</code></pre>
</div>

<div>
Hi, 
 
Use 

<pre><code class="code-1 nolinks" id="code-20-42710040-1">Isnull(column1,0) as newcolumn1 
</code></pre>
 
to columns, in your query
</div>

<div>
Russell Fox, I'm totally confused 
 
HuaMin Chen - OK..after I get that, how do I add an expression to COUNT the total of nulls?
</div>

<div>
My field has the following: 
 
50 
0 
0 
75 
0 
0 
0 
 
I want to count the number of nulls or zero and have it show 5
</div>

<div>
Use 

<pre><code class="code-1 nolinks" id="code-20-42710046-1">Count(Isnull(column1,0)) as countcolumn1
</code></pre>
 
in your query
</div>

<div>
I'm not getting it to work. &nbsp;Where would I put this? 
 

<pre><code class="code-10 nolinks" id="code-20-42710052-1">SELECT p21_view_inv_bin.location_id, p21_view_inv_bin.bin, p21_view_inv_bin.item_id, p21_view_inv_bin.quantity, p21_view_lot_bin_xref.lot_cd,
 (SELECT lot_attribute_value
 FROM p21_view_lot_x_lot_attribute_value
 WHERE (lot_uid = p21_view_lot.lot_uid) AND (lot_attribute_uid = 2)) AS 'Mfg Lot', p21_view_inv_bin.date_created, p21_view_lot.date_created AS Expr1, 
 p21_view_lot_bin_xref.bin_cd, p21_view_lot.qty_on_hand, p21_view_inv_mast.extended_desc, p21_view_inv_loc.product_group_id, 
 p21_view_inv_loc.putaway_rank, p21_view_lot_bin_xref.qty_linked, p21_view_lot_bin_xref.qty_allocated, p21_view_lot_bin_xref.date_created AS Expr2, 
 p21_view_lot_bin_xref.date_last_modified, p21_view_lot_bin_xref.qty_linked - p21_view_lot_bin_xref.qty_allocated AS unallocated, 
 CASE WHEN bin LIKE 'BHOT%' THEN 'BHOT' WHEN bin LIKE 'CART%' THEN 'CART' WHEN bin LIKE 'BG6%' THEN 'BG6' WHEN bin LIKE 'BHOT%' THEN 'BHOT' WHEN
 BIN LIKE 'REC' THEN 'RECEIVING' WHEN BIN LIKE 'ASSEMBLY' THEN 'ASSEMBLY' WHEN bin LIKE 'PACK%' THEN 'PACKAGING' WHEN bin LIKE 'kit%' THEN 'KITTING'
 WHEN BIN LIKE 'HOT1%' THEN 'HOT1' WHEN bin LIKE 'pKg%' THEN 'PACK' WHEN bin LIKE 'SH%' THEN 'SH' WHEN bin LIKE 'BG3%' THEN 'BG3' WHEN bin LIKE 'BH%'
 THEN 'BH' WHEN bin LIKE '4Q%' THEN 'PACK' WHEN bin LIKE '4R%' THEN 'PACK' WHEN bin LIKE '4NW%' THEN 'PACK' WHEN bin LIKE '4O%' THEN 'PACK' WHEN BIN
 LIKE 'TOWER%' THEN 'TOWER' WHEN bin LIKE '4ASM%' THEN 'ASSEMBLY' WHEN BIN LIKE '4P%' THEN 'PACK' WHEN BIN LIKE 'REWORK%' THEN 'REWORK' WHEN
 BIN LIKE 'REC%' THEN 'RECEIVING' WHEN BIN LIKE 'BRANCH%' THEN 'BRANCH' WHEN BIN LIKE 'QC001%' THEN 'QC' WHEN BIN LIKE 'DENTON%' THEN 'QC' ELSE
 'AAAAAAAAAAAAAAAAA' END AS [Group], ISNULL(p21_view_lot_bin_xref.qty_allocated, 1) AS Expr4
FROM p21_view_lot_bin_xref INNER JOIN
 p21_view_lot ON p21_view_lot_bin_xref.lot_cd = p21_view_lot.lot AND p21_view_lot_bin_xref.location_id = p21_view_lot.location_id INNER JOIN
 p21_view_inv_bin ON p21_view_lot_bin_xref.bin_cd = p21_view_inv_bin.bin AND p21_view_lot_bin_xref.inv_mast_uid = p21_view_inv_bin.inv_mast_uid AND 
 p21_view_lot_bin_xref.location_id = p21_view_inv_bin.location_id INNER JOIN
 p21_view_inv_mast ON p21_view_inv_bin.inv_mast_uid = p21_view_inv_mast.inv_mast_uid INNER JOIN
 p21_view_inv_loc ON p21_view_lot.location_id = p21_view_inv_loc.location_id AND p21_view_lot.inv_mast_uid = p21_view_inv_loc.inv_mast_uid
WHERE (p21_view_inv_bin.quantity &gt; 0) AND (p21_view_lot_bin_xref.qty_linked &gt; 0) AND (p21_view_inv_bin.location_id = 101) AND (p21_view_lot.qty_on_hand &gt; 0) AND 
 (p21_view_lot_bin_xref.qty_linked - p21_view_lot_bin_xref.qty_allocated &gt; 0) AND (p21_view_inv_bin.bin LIKE 'BH%' OR
 p21_view_inv_bin.bin LIKE 'ASS%' OR
 p21_view_inv_bin.bin LIKE 'CAR%' OR
 p21_view_inv_bin.bin LIKE 'HOT%' OR
 p21_view_inv_bin.bin LIKE 'REC%' OR
 p21_view_inv_bin.bin LIKE 'BG3%' OR
 p21_view_inv_bin.bin LIKE 'BG6%' OR
 p21_view_inv_bin.bin LIKE 'Sh%' OR
 p21_view_inv_bin.bin LIKE 'SP%' OR
 p21_view_inv_bin.bin LIKE 'Tower%' OR
 p21_view_inv_bin.bin LIKE '4P%' OR
 p21_view_inv_bin.bin LIKE '4r%' OR
 p21_view_inv_bin.bin LIKE '4q%' OR
 p21_view_inv_bin.bin LIKE '4nw%' OR
 p21_view_inv_bin.bin LIKE '4asm%' OR
 p21_view_inv_bin.bin LIKE 'REWORK%' OR
 p21_view_inv_bin.bin LIKE 'DENTON%' OR
 p21_view_inv_bin.bin LIKE 'BRANCH%' OR
 p21_view_inv_bin.bin LIKE 'REC%' OR
 p21_view_inv_bin.bin LIKE 'KIT%')
ORDER BY [Group], p21_view_inv_bin.bin, p21_view_inv_bin.item_id
</code></pre>
</div>

<div>
Put 
 
Isnull(p21_view_inv_bin.quantity,0) quantity 
 
instead of 
 
p21_view_inv_bin.quantity
</div>

<div>
Are you wanting to add this count to the Report Designer report or as a value in the query? 
 
I'm guessing as a summary count in the report. &nbsp;If so take a look at COUNT: 
<a href="https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-count-function?view=sql-server-2017" rel="ugc">https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-count-function?view=sql-server-2017</a> 
 
I'm not a SQL Server/Report Designer person but it appears that the COUNT with some IIF magic can count zeros: 
<a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9fa847b3-4606-420b-8f2a-0458a086e95b/need-help-with-a-count-expression-in-report-builder-30?forum=sqlreportingservices" rel="ugc nofollow">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9fa847b3-4606-420b-8f2a-0458a086e95b/need-help-with-a-count-expression-in-report-builder-30?forum=sqlreportingservices</a>
</div>

Project Manager

Sue Taylor

<div>
In SQL the COUNT() function will increment by 1 for ANY non-null value. 
 
So 
&nbsp; &nbsp; &nbsp; COUNT(case when [column_here] = 0 then 1 else null end) &nbsp;as count_alias 
 
or, this will achieve the same outcome 
 
&nbsp; &nbsp; SUM(case when [column_here] = 0 then 1 else 0 end) &nbsp;as sum_alias 
 
But I don't see how you are going to include that logic into the SQL query you just posted. 
 
[edit]i.e. There is no GROUP BY clause and so I &nbsp;don't see how you will be counting anything. 
ps, I really would not encourage you to name a column &quot;Group&quot;
</div>

<div>
Yes, I want to put this in the Report Designer and not the query 
 
I want to add an expression. &nbsp; The ones I have tried did not give me the results I anticipated.
</div>

<div>
I currently have the following Expression in the Report Designer&quot; &nbsp; &nbsp;=count(iif(Fields!unallocated.Value = &quot;0&quot;, 1, Nothing)) 
 
But the result is 0 when I expect it to be a number greater than 0 
 
In the attachment, All of my results are zero but in the one I have expanded out, I would expect the count to be 10 
<a href="https://filedb.experts-exchange.com/incoming/2018/10_w42/1394474/My-Results.png" target="_blank" class="file-inline" title="My results (128 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>My-Results.png</a>
</div>

My-Results.png

<div>
<div class="content wysiwyg-content">
SQL 2012 - Visual Studio 2012 
 
I have created a report in Visual Studio that has one field that is a Decimal (19,9) that I have it displaying as a blank if the number is zero. &nbsp;There are a lot of zeros in this column. &nbsp;I need to count the number of zeros. &nbsp;How can I achieve this? 
<a href="https://filedb.experts-exchange.com/incoming/2018/10_w42/1394466/Unallocated-Summary.rdl" target="_blank" class="file-inline" title="Current Report (80 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Unallocated-Summary.rdl</a>
</div>
</div>

Unallocated-Summary.rdl

SQL 2012 - Visual Studio 2012

I have created a report in Visual Studio that has one field that is a Decimal (19,9) that I have it displaying as a blank if the number is zero.  There are a lot of zeros in this column.  I need to count the number of zeros.  How can I achieve this?

Help with counting the number of zeros

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Microsoft Visual Studio is an integrated development environment (IDE) from Microsoft. It is used to develop computer programs for Microsoft Windows, as well as web sites, web applications and web services. Visual Studio uses Microsoft software development platforms such as Windows API, Windows Forms, Windows Presentation Foundation, Windows Store and Microsoft Silverlight. It can produce both native code and managed code.