Complex query!

[MrProgrammer]

I have two tables named "Main" and "RegionNames". Their descriptions are like below with sample records:

Main:
CutomerID Address Salary($):
2124441256 Gultepe 4000
3463112356 Antep 6000
2124447890 Pendik 7000
3463115838 Antep 3000
2124449900 Levent 4500
2124445543 Taksim 6200
.......... ....... ....

RegionNames:
RegionCode RegionName:
212444 ISTANBUL
346311 MARAS
...... ........

As can be seen, region codes are hidden in CustomerId (first 6 digits) fields. I want to prepare a report that show how many customers who have salary more and less then 5000$ there are in each region. A sample output must be like this:
Salary Less Than 5000$ Salary More Than 5000$
RegionCode RegionName Count Sum(Salary) Count Sum(Salary)
212444 ISTANBUL 2 8500 2 13200
346311 MARAS 1 3000 1 6000



How can I achieve this result? Do you have any ideas??? Thanks!

 

[john76]

I'd say a few correlated subqueries would do the trick...

SELECT    RegionCode,
          RegionName,
          SalaryLessCnt = (SELECT    COUNT(*)
                             FROM    Main
                             WHERE   SUBSTRING(CONVERT(varchar, LTRIM(RTRIM(Main.CustomerID))), 1, 6) = RegionNames.RegionCode
                               AND   Main.Salary < 5000
                           )
          ,
          SalaryLessSum = (SELECT    SUM(Salary)
                             FROM    Main
                             WHERE   SUBSTRING(CONVERT(varchar, LTRIM(RTRIM(Main.CustomerID))), 1, 6) = RegionNames.RegionCode
                               AND   Main.Salary < 5000
                           )
          ,
          SalaryMoreCnt = (SELECT    COUNT(*)
                             FROM    Main
                             WHERE   SUBSTRING(CONVERT(varchar, LTRIM(RTRIM(Main.CustomerID))), 1, 6) = RegionNames.RegionCode
                               AND   Main.Salary > 5000
                           )
          ,
          SalaryMoreSum = (SELECT    SUM(Salary)
                             FROM    Main
                             WHERE   SUBSTRING(CONVERT(varchar, LTRIM(RTRIM(Main.CustomerID))), 1, 6) = RegionNames.RegionCode
                               AND   Main.Salary > 5000
                           )
GO

I won't comment on efficiency as I'm sure there is a "better" way, but I personally like corr. subqueries as I think they are easier to read and see what is happening by someone looking at the code.

Hope this helps,

John

 

[Pattycake245]

I've tested this and it looks like it works:

select b.regioncode,b.regionname, isnull(sum(case when a.salary < 5000 then 1 else 0 end),0) [Salary Less Than 5000],
isnull(sum(case when a.salary < 5000 then a.salary end),0) [sum salary -],
isnull(sum(case when a.salary > 5000 then 1 else 0 end),0) [Salary Greater Than 5000],
isnull(sum(case when a.salary > 5000 then a.salary end),0) [sum salary +]
from main a join RegionNames b on left(a.customerid,6)=b.regioncode
group by b.regioncode,b.regionname

Tim