- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

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
Comment

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?

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?

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

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.

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.

No one has followed this question yet.

Copyright 2019 Redgate Software.
Privacy Policy