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

technette gravatar image

technette
1.2k 81 97 107

(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

jimbobmcgee gravatar image

jimbobmcgee
359 5 6 7

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x108
x14

asked: Apr 13, 2011 at 03:02 PM

Seen: 1033 times

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