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?
 
ErnariashConnect With a Mentor Commented:
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
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.