How do I count the results from a Coalesce Statement?

I finally have the end results working properly. The statement looks in three different places in a specific order to find the answer. The first place that is not null populates the field.

However, now I must build a validation file on at least one of the resulting fields in the Query.

I have no idea how to add a COUNT(*) to the statement. Everything errors out and all the answers online do not fully explain how to accomplish this.

I need a Total # of Lines in the query as my result.

Attached is my query. It is MS SQL Server Management Studio v17.4.[embedCostItems.xlsx=file 1443596]
I have also attached some sample data. My answer for the Count(Coalesce etc) should be 37, because there are 37 rows in the excel document.
HB_CostItem_ForEE.sql

Microsoft SQL ServerSQL

Last Comment

Becky Edwards

8/22/2022 - Mon

lcohan

2/12/2020

How about to include the

select @@ROWCOUNT;

Open in new window

immediately after the SELECT statement that gives you the correct record set as this will show you exactly that: "Returns the number of rows affected by the last statement. "

https://docs.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver15

Alternatively you could declare/fill a variable with this "count" like below and return that variable:

declare @row_count int;

--put your select/insert/update statement here
---- select * from mytable where blablabla...;

set @row_count = @@ROWCOUNT;
print @row_count;
--return @row_count

Open in new window

Becky Edwards

2/14/2020

ASKER

Questions:
You have instructions to put your select statement here...

then
set @row_count = @@Rowcount; Q: Where does this go?
After the Select statement but before the FROM ?
After the FROM but before the WHERE?

then
print @row_Count;
same Questions:Q: Where does this go?
After the Select statement but before the FROM ?
After the FROM but before the WHERE?

then
you have return @row_count but you have it commented out.
Is this not used?
Why is it in the solution?

ASKER CERTIFIED SOLUTION

lcohan

2/14/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Becky Edwards

2/17/2020

ASKER

Thank you Icohan. This was helpful and has gotten me part of the way to my full solution. I learned a lot from your explanations and was able to build the query I need. I will open a new question to find out how to incorporate this query without having to run the first one, perhaps with a view or stored procedure.

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck