- Home /

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

Comment

Scot Hauder

Best Answer

**Answer** by jimbobmcgee
·
Apr 13, 2011 at 05:47 PM

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?

technette

Tim

KenJ

Magnus Ahlkvist

ThomasRushton

Matt Whitfield

Tim

KenJ

Magnus Ahlkvist

ThomasRushton

Matt Whitfield

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.

Copyright 2018 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges