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!
LVL 5
allanau20Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChetOS82Commented:
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
0
brad2575Commented:
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
0
brad2575Commented:
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
      
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SharathData EngineerCommented:
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
0
ErnariashCommented:
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

0
ChetOS82Commented:
@Ernariash: If s.TotalPaid is null in any instance, then SUM(s.TotalPaid) will be null.  Therefore, you need to put ISNULL inside SUM.
0
ErnariashCommented:
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

0
ChetOS82Commented:
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.
0
ErnariashCommented:

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

0
allanau20Author Commented:
Thanks all -- I'll try it...
0
allanau20Author Commented:
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....
0
ErnariashCommented:
No problem, :) Just add the Where on the subquery.

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allanau20Author Commented:
Thanks again Ernariash.
I think I've enough to work with .. maybe I'll use a derived table for the subset ...
0
SharathData EngineerCommented:
Did you check all the solutions posted?
0
allanau20Author Commented:
Thanks for your help Sharath_123.
Yes, I tried yours but there was an error so I just moved on to the next solution.
 Thanks for your replies.
0
SharathData EngineerCommented:
You should have post the error. As for i know my query should work well for your requirement.
0
allanau20Author Commented:
so, sorry.
0
SharathData EngineerCommented:
its ok. you got the solution :)
0
allanau20Author Commented:
thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.