help with Coalesce function

Discussion:

(too old to reply)

SqlBeginner

2007-04-25 16:42:01 UTC

Hi,

I have this query,

Select state, zipcode, Sum(Sales) As Sales, Sum(TotIssues) As TotIssues,
'1 : ' +
convert(varchar(20),Convert(Decimal(10,2),round(convert(decimal(8,2),NULLIF(sum(TotIssues),0)) / convert(decimal(8,2),NULLIF(sum(sales),0)),2))) as Density
From (Select state, Substring(zipcode,1,5) As zipcode, 1 As Sales, 0 As
TotIssues
From dbo.Table1 Sales Where conDate is not null and candate is null
Union All
Select state_code As state, zipcode, 0 As Sales, 1 As TotIssues
from dbo.Table2
where Closed_Date is not null) As x
Group by state, zipcode
Order by state, zipcode

If "Density" column is NULL i want to display "Sales:TotIssues". How to do
this? Kindly help me in this.

Regards
Pradeep

Mike C#

2007-04-25 17:09:54 UTC

Permalink

Not sure what you're getting at here. Post your DDL (Table CREATE
statements), a few lines of sample data and your expected result please.

Post by SqlBeginner
Hi,
I have this query,
Select state, zipcode, Sum(Sales) As Sales, Sum(TotIssues) As TotIssues,
'1 : ' +
convert(varchar(20),Convert(Decimal(10,2),round(convert(decimal(8,2),NULLIF(sum(TotIssues),0))
/ convert(decimal(8,2),NULLIF(sum(sales),0)),2))) as Density
From (Select state, Substring(zipcode,1,5) As zipcode, 1 As Sales, 0 As
TotIssues
From dbo.Table1 Sales Where conDate is not null and candate is null
Union All
Select state_code As state, zipcode, 0 As Sales, 1 As TotIssues
from dbo.Table2
where Closed_Date is not null) As x
Group by state, zipcode
Order by state, zipcode
If "Density" column is NULL i want to display "Sales:TotIssues". How to do
this? Kindly help me in this.
Regards
Pradeep

SqlBeginner

2007-04-25 18:04:02 UTC

Permalink

Hi Mike,

For the query which I have given the

Current Output:
----------------
state zipcode sales totissue Density
aaa bbb 10 20 1:2
aa1 bb1 20 40 1:2
...
...
xyz abc 10 0 NULL

But where ever total issue is 0 or density is NULL i want to automatically
display sales:totissues i.e.,

Expected Output:
----------------

aaa bbb 10 20 1:2
aa1 bb1 20 40 1:2
...
...
xyz abc 10 0 10:0

Hope i have made the query clear now.

Regards
pradeep

Post by Mike C#
Not sure what you're getting at here. Post your DDL (Table CREATE
statements), a few lines of sample data and your expected result please.

Mike C#

2007-04-25 18:49:52 UTC

Permalink

I noticed that by your request below, if SUM(Sales) is 0 then you will get a
divide by zero exception. I've left that part of your request as-is (it is
easily fixed though it you want it to return TotIssues:Sales for SUM(Sales)
= 0 as well as SUM(Sales) IS NULL. ***UNTESTED*** (No DDL, no test):

SELECT state,
zipcode,
SUM(Sales) AS Sales,
SUM(TotIssues) AS TotIssues,
CASE WHEN COALESCE(SUM(TotIssues), 0) = 0
OR SUM(Sales) IS NULL
THEN CAST(COALESCE(SUM(TotIssues), 0) AS VARCHAR(20)) + ':'
+ CAST(COALESCE(SUM(Sales), 0) AS VARCHAR(20))
ELSE '1:' + CAST(COALESCE(SUM(CAST(TotIssues AS DECIMAL(8, 2))), 0)
/
SUM(Sales) AS VARCHAR(10)) END AS Density,
FROM (
SELECT state,
SUBSTRING(zipcode,1,5) AS zipcode,
1 AS Sales,
0 AS TotIssues
FROM dbo.Table1 Sales
WHERE conDate IS NOT NULL AND candate IS NULL
UNION ALL
SELECT state_code AS state,
zipcode,
0 AS Sales,
1 AS TotIssues
FROM dbo.Table2
WHERE Closed_Date IS NOT NULL
) AS x
GROUP BY state, zipcode
ORDER BY state, zipcode

Post by SqlBeginner
Hi Mike,
For the query which I have given the
----------------
state zipcode sales totissue Density
aaa bbb 10 20 1:2
aa1 bb1 20 40 1:2
...
...
xyz abc 10 0 NULL
But where ever total issue is 0 or density is NULL i want to automatically
display sales:totissues i.e.,
----------------
aaa bbb 10 20 1:2
aa1 bb1 20 40 1:2
...
...
xyz abc 10 0 10:0
Hope i have made the query clear now.
Regards
pradeep

Post by Mike C#
Not sure what you're getting at here. Post your DDL (Table CREATE
statements), a few lines of sample data and your expected result please.

Anastasiosyal

2007-04-25 18:22:01 UTC

Permalink

Hi there,

assuming that your current sql works fine all you need to do is add an
IsNull check like this:

Select state, zipcode, Sum(Sales) As Sales, Sum(TotIssues) As TotIssues,
IsNull (
'1 : ' +
convert(varchar(20),Convert(Decimal(10,2),round(convert(decimal(8,2),NULLIF(sum(TotIssues),0)) / convert(decimal(8,2),NULLIF(sum(sales),0)),2))),
'Sales: TotIssues' ) as Density
From (Select state, Substring(zipcode,1,5) As zipcode, 1 As Sales, 0 As
TotIssues
From dbo.Table1 Sales Where conDate is not null and candate is null
Union All
Select state_code As state, zipcode, 0 As Sales, 1 As TotIssues
from dbo.Table2
where Closed_Date is not null) As x
Group by state, zipcode
Order by state, zipcode

Hope this helps,

AnastasiosYal

Post by SqlBeginner
Hi,
I have this query,
Select state, zipcode, Sum(Sales) As Sales, Sum(TotIssues) As TotIssues,
'1 : ' +
convert(varchar(20),Convert(Decimal(10,2),round(convert(decimal(8,2),NULLIF(sum(TotIssues),0)) / convert(decimal(8,2),NULLIF(sum(sales),0)),2))) as Density
From (Select state, Substring(zipcode,1,5) As zipcode, 1 As Sales, 0 As
TotIssues
From dbo.Table1 Sales Where conDate is not null and candate is null
Union All
Select state_code As state, zipcode, 0 As Sales, 1 As TotIssues
from dbo.Table2
where Closed_Date is not null) As x
Group by state, zipcode
Order by state, zipcode
If "Density" column is NULL i want to display "Sales:TotIssues". How to do
this? Kindly help me in this.
Regards
Pradeep