question

technette avatar image
technette asked

Row count < 1 Return 0

How can I correctly write this select statement to return 0 if the row count is less than 1? UPDATE @Totaltbl SET TTLPerRevAj = X.TTLPerRevAj , TTLYTDRevAj = X.TTLYTDRevAj, TTLJTDRevAj = X.TTLJTDRevAj FROM( Select SUM(Table2.Col1) TTLPerRevAj, SUM(Table2.Col2)TTLYTDRevAj, SUM(Table2.Col3) TTLJTDRevAj FROM dbo.Table2 Where (Table2.Job_Number = @Job_Number) and (Table2.Period_Year = @Period_Year) and (Table2.Period_Number = @Period_Number) Group By Table2.Job_Number, Table2.Period_Year, Table2.Period_Number) X
selectrow-counts
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
jimbobmcgee avatar image
jimbobmcgee answered
I'm not 100% sure what you are after here, so bear with me. Your subselect (X) is only going to return one run, by rights, because you are calling `GROUP BY` on the same fields as your `WHERE` (and that `WHERE` is a pure `=` filter). Do you want everything other than the job, year and period you select to become 0 in your table? If so, for readability, try doing it in two queries: UPDATE @Totaltbl SET TTLPerRevAj = 0, TTLYTDRevAj = 0, TTLJTDRevAj = 0 UPDATE @Totaltbl SET TTLPerRevAj = X.TTLPerRevAj, TTLYTDRevAj = X.TTLYTDRevAj, TTLJTDRevAj = X.TTLJTDRevAj FROM ( Select SUM(Table2.Col1) TTLPerRevAj, SUM(Table2.Col2) TTLYTDRevAj, SUM(Table2.Col3) TTLJTDRevAj FROM dbo.Table2 Where (Table2.Job_Number = @Job_Number) and (Table2.Period_Year = @Period_Year) and (Table2.Period_Number = @Period_Number) Group By Table2.Job_Number, Table2.Period_Year, Table2.Period_Number ) X If you really want to do it in one query, try: UPDATE @Totaltbl SET TTLPerRevAj = X.TTLPerRevAj , TTLYTDRevAj = X.TTLYTDRevAj, TTLJTDRevAj = X.TTLJTDRevAj FROM ( SELECT TTLPerRevAj = SUM(CASE WHEN t2.Job_Number = @Job_Number AND t2.Period_Year = @Period_Year AND t2.Period_Number = @Period_Number THEN t2.Col1 ELSE 0 END), TTLYTDRevAj = SUM(CASE WHEN t2.Job_Number = @Job_Number AND t2.Period_Year = @Period_Year AND t2.Period_Number = @Period_Number THEN t2.Col2 ELSE 0 END), TTLJTDRevAj = SUM(CASE WHEN t2.Job_Number = @Job_Number AND t2.Period_Year = @Period_Year AND t2.Period_Number = @Period_Number THEN t2.Col3 ELSE 0 END) FROM dbo.Table2 t2 GROUP BY t2.Job_Number, t2.Period_Year, t2.Period_Number ) X (but I bet there are better ways.) Otherwise, you need to be a bit more specific about what it is you are really trying to do?
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you jimbobmcgee! This works, but now I get my divide by zero error back. I don't suppose this could be adjusted to eliminate that error?
0 Likes 0 ·
This is one of many sets in a table. At the end of the stored procedure, I wrote this to eliminate the divide by zero error: SELECT * FROM (select 1 a) b left join @Totaltbl on 1 = 1
0 Likes 0 ·
I don't see any division in your query, so I can't help you there. The typical way to resolve divide-by-zero is to use a `CASE` around the denominator: SELECT a, b, div = CASE b WHEN 0 THEN NULL ELSE a / b END FROM set Best I can do without a proper example. Edit your question (for formatting, at least) and include the division query, so we can see exactly what you are trying to do.
0 Likes 0 ·
I'm marking the original as answered because the question was answered. I'm working with report viewer and have put the division in the calculations. I have to somehow work with this from there. I tried a user defined function but get errors when I try to use it.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.