Avatar of Sue Taylor
Sue TaylorFlag for United States of America asked on

Help with counting the number of zeros

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
SQLMicrosoft Visual Studio

Avatar of undefined
Last Comment
Sue Taylor

8/22/2022 - Mon
Russell Fox

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 Chan

Hi,

Use
Isnull(column1,0) as newcolumn1 

Open in new window


to columns, in your query
ASKER
Sue Taylor

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Sue Taylor

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 Chan

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

Open in new window


in your query
ASKER
Sue Taylor

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Peter Chan

Put

Isnull(p21_view_inv_bin.quantity,0) quantity

instead of

p21_view_inv_bin.quantity
Peter Chan

Put

Isnull(p21_view_inv_bin.quantity,0) quantity

instead of

p21_view_inv_bin.quantity
slightwv (䄆 Netminder)

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

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"
ASKER
Sue Taylor

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.
ASKER
Sue Taylor

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Sue Taylor

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question