CODEWITHSUNDEEP
sql-serverleft-join
Laughy
Laughy

Reputation: 1979

how to combine inner join, sub queries, sum and where condition

suppose I have a table like the below:

Employee No | Wage Type|Wages|Company Code|

For each employee no, I want to create a table such as the following (I know the number of wage types)

Unique Employee No|Sum(Wage Type 1)|Sum(Wage Type 2)| Company Code

I am using the following code, however it does not works...

select [Pers No ] ,["Co   Code"],[Company Code],
from [dbo].[Wages] as a 
    left join(
        select sum([[Total Wages])as [Total Wage Type 1 for Aug] 
        from [dbo].[Wages] 
        where [Wage Description] ='Wage Type 1' 
    ) as b on a.[Pers No ]=b.[Pers No ]
    left join(
        select sum([Total Wages]) as [Total Wage Type 2 for Aug] 
        from [dbo].[Wages] 
        where [Wage Description]='Wage Type 2'
    ) as c on b.[Pers No ] =c.[Pers No ]
group by[Pers No ], ["Co   Code"],[Company Code]
into [Total Wages Group by Pers No]

Basically, I want to get the total sum of wages for each wage type group by each employee. My idea was to initially group by employee then get the sum of each type of wages within a single select statement, however that doesn't works. Thats why I am using left join now...but it doesn't work as well.

Upvotes: 0

Views: 70

Answers (1)

jpw
jpw

Reputation: 44901

Using a conditional aggregation could probably work:

select 
  [Pers No ],[Company Code],
  sum(case when [Wage Description] = 'OW for CPF' then [PwC_Totals] else 0 end) as [Total OW for Aug] ,
  sum(case when [Wage Description] = 'AW for CPF' then [PwC_Totals] else 0 end) as [Total AW for Aug] 
from [dbo].[OW and  AW - aug 14_cleaned] 
group by [Pers No ], [Company Code]

The column names in your table isn't all clear, so you might have to adjust them.

Upvotes: 1

Related Questions