CODEWITHSUNDEEP
sqlsql-servert-sql
desi
desi

Reputation: 476

How to sum the values in a one row by grouping from multiples rows

I have one table with various teams and the Sponsor names who manages those teams. The expenditure for each Sponsor to manage those teams are categorized by play in various columns in the same table. Below is a sample table with Team names, Sponsor names, and expenditures... Can someone please help me how to group on each play summing the expenditures in one row.

Table1 sample 

TEAM1   TEAM2   TEAM3   Sponsor Expenses1   Expenses2
A       B       C       Play1   $1,550      $2,300
A       B       C       Play2   $1,580      $2,225
AA      BB      CC      Play1   $2,320      $2,900
AA      BB      CC      Play2   $,2464      $3,672
AAA     BBB     CCC     Play1   $3,730      $2,890
AAA     BBB     CCC     Play2   $3,852      $2,962

I want the output in another table as follows grouped by each play and sum the expenditures. Please help me.

OUTPUT 

TEAM1   TEAM2   TEAM3   Play1   Play2   Difference
A       B       C       $3,850   $3,805  $-45
AA      BB      CC      $5,220   $6,136  $916
AAA     BBB     CCC     $6,620   $6,814  $194

Upvotes: 0

Views: 53

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

Assuming you don't need to go dynamic, a simple conditional aggregation should do the trick

Example

Declare @Team1 varchar(50) = null
Declare @Team2 varchar(50) = null   -- Try 'BB'
Declare @Team3 varchar(50) = null

Select Team1
      ,Team2
      ,Team3
      ,Play1 = sum(case when Sponsor='Play1' then Expenses1+Expenses2 end )
      ,Play2 = sum(case when Sponsor='Play2' then Expenses1+Expenses2 end )
      ,Diff  = sum(case when Sponsor='Play2' then Expenses1+Expenses2 end )-sum(case when Sponsor='Play1' then Expenses1+Expenses2 end )
 from  @YourTable
 Where Team1 = IsNull(@Team1,Team1)
   and Team2 = IsNull(@Team2,Team2)
   and Team3 = IsNull(@Team3,Team3)
 Group By Team1,Team2,Team3

Returns

enter image description here

Upvotes: 4

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use pivot and difference as below:

Select *, [Difference] = [Play2] - [Play1] from (
    Select Team1, Team2, Team3, Sponsor, Expenses1+Expenses2 as Expenses from #Expensedata ) a
pivot(sum(Expenses) for sponsor in ([Play1],[Play2])) p

Output as below:


+-------+-------+-------+---------+---------+------------+
| Team1 | Team2 | Team3 |  Play1  |  Play2  | Difference |
+-------+-------+-------+---------+---------+------------+
| A     | B     | C     | 3850.00 | 3805.00 |     -45.00 |
| AA    | BB    | CC    | 5220.00 | 6136.00 |     916.00 |
| AAA   | BBB   | CCC   | 6620.00 | 6814.00 |     194.00 |
+-------+-------+-------+---------+---------+------------+

Upvotes: 2

Caius Jard
Caius Jard

Reputation: 74605

You need a query that groups on Team1, Team2, Team3 and sums like this:

SUM(CASE WHEN sponsor = 'play1' THEN expenses1 + expenses2 END) as Play1

A similar pattern for play2, and then doing the difference should be a doddle!

Upvotes: 0

Related Questions