[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

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!
0
allanau20
Asked:
allanau20
  • 6
  • 4
  • 4
  • +2
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now