We help IT Professionals succeed at work.

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
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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 EdwardsEpic Clarity Developer

Author

Commented:
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?
Database Analyst
CERTIFIED EXPERT
Commented:
OK please take the code below and replace the SELECT with your own then try to run it in management studio;
RETURN statement is commented out as that cannot be used in SSMS query but if you have to use the sample code in a stored procedure...PRINT is  not much help.

declare @row_count int;

SELECT * FROM myTable WHERE col1=123 AND col2 = 'test' ORDER BY Id;  --- replace this with your select statement....i

set @row_count = @@ROWCOUNT;
print @row_count;

Open in new window

Becky EdwardsEpic Clarity Developer

Author

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