x

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
more ▼

asked Apr 13, 2011 at 03:02 PM in Default

avatar image

technette
1.4k 100 113 120

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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?

more ▼

answered Apr 13, 2011 at 05:47 PM

avatar image

jimbobmcgee
399 5 8 12

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?

Apr 13, 2011 at 06:37 PM technette

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

Apr 13, 2011 at 06:41 PM technette

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.

Apr 13, 2011 at 07:03 PM jimbobmcgee

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.

Apr 13, 2011 at 07:39 PM technette
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x148
x17

asked: Apr 13, 2011 at 03:02 PM

Seen: 1304 times

Last Updated: Apr 13, 2011 at 09:45 PM

Copyright 2016 Redgate Software. Privacy Policy