We help IT Professionals succeed at work.

Help with counting the number of zeros

74 Views
Last Modified: 2018-10-17
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?
Unallocated-Summary.rdl
Comment
Watch Question

Russell FoxDatabase Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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:

SELECT LEN(CAST([YourField] AS VARCHAR(2000)) - LEN(REPLACE(CAST([YourField] AS VARCHAR(2000), '0', ''))

Open in new window

Peter ChanProblem resolver

Commented:
Hi,

Use
Isnull(column1,0) as newcolumn1 

Open in new window


to columns, in your query
Sue TaylorProject Manager

Author

Commented:
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?
Sue TaylorProject Manager

Author

Commented:
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
Peter ChanProblem resolver

Commented:
Use
Count(Isnull(column1,0)) as countcolumn1

Open in new window


in your query
Sue TaylorProject Manager

Author

Commented:
I'm not getting it to work.  Where would I put this?

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 > 0) AND (p21_view_lot_bin_xref.qty_linked > 0) AND (p21_view_inv_bin.location_id = 101) AND (p21_view_lot.qty_on_hand > 0) AND 
                      (p21_view_lot_bin_xref.qty_linked - p21_view_lot_bin_xref.qty_allocated > 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

Open in new window

Peter ChanProblem resolver

Commented:
Put

Isnull(p21_view_inv_bin.quantity,0) quantity

instead of

p21_view_inv_bin.quantity
Peter ChanProblem resolver

Commented:
Put

Isnull(p21_view_inv_bin.quantity,0) quantity

instead of

p21_view_inv_bin.quantity
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.  If so take a look at COUNT:
https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-count-function?view=sql-server-2017

I'm not a SQL Server/Report Designer person but it appears that the COUNT with some IIF magic can count zeros:
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
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
In SQL the COUNT() function will increment by 1 for ANY non-null value.

So
      COUNT(case when [column_here] = 0 then 1 else null end)  as count_alias

or, this will achieve the same outcome

    SUM(case when [column_here] = 0 then 1 else 0 end)  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  don't see how you will be counting anything.
ps, I really would not encourage you to name a column "Group"
Sue TaylorProject Manager

Author

Commented:
Yes, I want to put this in the Report Designer and not the query

I want to add an expression.   The ones I have tried did not give me the results I anticipated.
Sue TaylorProject Manager

Author

Commented:
I currently have the following Expression in the Report Designer"    =count(iif(Fields!unallocated.Value = "0", 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
My-Results.png
Project Manager
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.