asked on

Help W/ Sql Script

Hi Experts,

Need help w/ Sql script (this is for MS-SQL 2005).

Say we have two tables:

Regionid Region
-------- ------
1 West
2 East
3 North
4 South

Sales

sid regionid sproduct
--- -------- -------- ......
1 1 shirt
2 1 pant
3 1 shirt
4 1 tree
5 2 shirt
6 2 inservice
7 3 outservice
8 3 tree

Although in the sales table there are no sales product for South region, but the sql
result should look like this:

Region Total Count Total Paid
------ ---------- ----------
West (something) (something)
East (something) (something)
North (something) (something)
--> South 0 0 <--

The sql I have looks like this, but its some modification

SELECT
r.Region
,Count (1) AS 'Total Count'
,Sum(s.TotalPaid) 'Total Paid'
FROM Sales s
INNER JOIN Region r ON s.regionid = r.regionid
GROUP BY r.Region

I think using a left join might work ...

TIA!

ChetOS82

yes, use left outer join instead.

SELECT
r.Region
, COUNT(*) as 'Total Count'
, SUM(s.TotalPaid) as 'Total Paid'
FROM Sales s
LEFT OUTER JOIN Region r ON s.Regionid = r.Regionid
GROUP BY r.Region

brad2575

change it to this:

SELECT
r.Region
,Count (1) AS 'Total Count'
,Sum(s.TotalPaid) 'Total Paid'
FROM Region r Left Outer JOin
Sales s R.regionid = S.regionid
GROUP BY r.Region

brad2575

if total paid can be blank/null change it to this:

SELECT
r.Region
,Count (1) AS 'Total Count'
,Sum(ISNULL(s.TotalPaid, 0)) 'Total Paid'
FROM Region r Left Outer JOin
Sales s R.regionid = S.regionid
GROUP BY r.Region

Sharath S

This will work for you.
SELECT R.Regionid,ISNULL(S.Count1,0) AS TotalCount, ISNULL(S.Paid,0) AS TotalPaid
FROM Region R
LEFT JOIN (SELECT Count(regionid) AS Count1, SUM(TotalPaid) AS Paid
FROM Sales
GROUP BY regionid) S
ON R.Regionid = S.Regionid

Ernariash

Hello allanau20, you almost got it...try this

SELECT 
  r.Region
  ,ISNULL(Count(s.regionid), 0) AS 'Total Count'
  ,ISNULL(Sum(s.TotalPaid), 0) 'Total Paid'
FROM Region r
	LEFT OUTER JOIN Sales s ON s.regionid = r.regionid
GROUP BY r.Region

Open in new window

ChetOS82

@Ernariash: If s.TotalPaid is null in any instance, then SUM(s.TotalPaid) will be null. Therefore, you need to put ISNULL inside SUM.

Ernariash

ChetOS82: the query will work...the null just do not add up...
you are confusing null + number = null...
you can try the query to test it...

--test summ null 
select sum(tot) from ( select 1 tot
 union all select 1 tot
 union all  select 1 tot
 union  all select null tot ) t
  
SELECT 
  r.Region
  ,ISNULL(Count(s.regionid), 0) AS 'Total Count'
  ,ISNULL(Sum(s.TotalPaid), 0) 'Total Paid'
FROM Region r
	LEFT OUTER JOIN Sales s ON s.regionid = r.regionid
GROUP BY r.Region

Open in new window

ChetOS82

You are right. I seem to remember that SUM() was returning null if a row was null, but I must have been thinking of something else.

Ernariash

hello ChetOS82:,
SUMM Null values are ignored.
SUM: Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
http://msdn.microsoft.com/en-us/library/aa259238(SQL.80).aspx

Actually count work different the count of null will be 0 then we would not need isnull on the count...this is the simplest:

SELECT 
  r.Region
  ,Count(s.regionid) AS 'Total Count'
  ,ISNULL(Sum(s.TotalPaid), 0) 'Total Paid'
FROM Region r
	LEFT OUTER JOIN Sales s ON s.regionid = r.regionid
GROUP BY r.Region

Open in new window

Allan

ASKER

Thanks all -- I'll try it...

Allan

ASKER

It's works Ernariash.

Sorry, but I failed to include something to the script.
It's a long script, so I'm just take the part that needs to change.
One thing I didn't include is that there are filter in the WHERE clause.

So, in the example, above if there is a filter on WHERE TotalPaid > 10 then South is not listed.
Is this possible.

Really appreciate your help ... and sorry for the late reply .. trying to multi-task here....

ASKER CERTIFIED SOLUTION

Ernariash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allan

ASKER

Thanks again Ernariash.
I think I've enough to work with .. maybe I'll use a derived table for the subset ...

Sharath S

Did you check all the solutions posted?

Allan