CODEWITHSUNDEEP
sqlsql-server
Arslan ud Din Shafiq
Arslan ud Din Shafiq

Reputation: 340

SQL Divided by Zero error and Syntax error in Case statement

I'm generating a report in SQL. I had an error of divided by zero. To resolve this error, I used case in SQL query. Now I'm getting a syntax error at 'as'. Is there anyone who can help me to resolve this issue?

create procedure dbo.CompanyAverageSales @startdate as varchar(50),@enddate 
as varchar(50)
As begin

select  datename(year,SO_SalesOrder.InvoicedDate) as [Year],
    datename(month,SO_SalesOrder.InvoicedDate) as [Month],
    l.Name as [Store Name], 
    case when sum(convert(int,SO_SalesOrder.Custom1))>0 then sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) as [Average Sales]
From SO_SalesOrder
inner join BASE_Location l on SO_SalesOrder.LocationId = l.LocationId 
inner join SO_SalesOrder_Line
on SO_SalesOrder.SalesOrderId = SO_SalesOrder_Line.SalesOrderId
inner join BASE_PaymentTerms
on BASE_PaymentTerms.PaymentTermsId = SO_SalesOrder.PaymentTermsId
where SO_SalesOrder.InvoicedDate >= @startdate and SO_SalesOrder.InvoicedDate <= @enddate
group by l.Name,
         datename(year,SO_SalesOrder.InvoicedDate),
         datename(month,SO_SalesOrder.InvoicedDate)


end

Upvotes: 1

Views: 228

Answers (5)

Michał Turczyn
Michał Turczyn

Reputation: 37367

This line generates error:

case when sum(convert(int,SO_SalesOrder.Custom1))>0 then sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) as [Average Sales]

You should close case statement with end:

case when sum(convert(int,SO_SalesOrder.Custom1))>0 then sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) end as [Average Sales]

Upvotes: 0

Muhammad Vakili
Muhammad Vakili

Reputation: 708

select
    sum(SO_SalesOrder_Line.SubTotal)/NULLIF(sum(convert(int,SO_SalesOrder.Custom1)),0)
FROM ...

This will returns Null for records with result with Devided by zero

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

A nice way to avoid division by zero in this case is to use NULLIF:

sum(SO_SalesOrder_Line.SubTotal) 
/ 
NULLIF(sum(convert(int,SO_SalesOrder.Custom1)), 0)

This will return NULL if sum(convert(int,SO_SalesOrder.Custom1)) = 0

If you want to return 0 then you can wrap the expression in a COALESCE:

COALESCE(sum(SO_SalesOrder_Line.SubTotal) 
         / 
         NULLIF(sum(convert(int,SO_SalesOrder.Custom1)), 0), 0)

Upvotes: 2

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

Add END before AS

case 
when sum(convert(int,SO_SalesOrder.Custom1))>0 
then 
    sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) 
Else 
    0
END as [Average Sales]

Upvotes: 5

SQL_M
SQL_M

Reputation: 2475

case when sum(convert(int,SO_SalesOrder.Custom1))>0 then 
sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) as 
[Average Sales

Should be:

 case when sum(convert(int,SO_SalesOrder.Custom1))>0 then 
 sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) 
 ELSE 0 -- your logic here
 END as [Average Sales

Upvotes: 2

Related Questions