Link to home
Create AccountLog in
Avatar of Becky Edwards
Becky EdwardsFlag for United States of America

asked on

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
Avatar of lcohan
lcohan
Flag of Canada image

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

Avatar of Becky Edwards

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
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.