CODEWITHSUNDEEP
sqlsql-servergroup-bygrouping
gberisha
gberisha

Reputation: 301

Add Values to Grouping Column

I am having a lot of trouble with a scenario that I think some of you might have come across.

(the whole thing about Business Trips, two tables, one filled with payments done on Business trips, and the other is about the Business Trips, so the first one has more Rows than the other, (there are more Payments that happened than Trips)) I have two tables, Table A and Table B.

Table A looks as follows

| TableA_ID | TableB_ID | PaymentMethod | ValuePayed  |
| 52        | 1         | Method1       |   23,2      |
| 21        | 1         | Method2       |   23,2      |
| 33        | 2         | Method3       |   23,2      |
| 42        | 1         | Method2       |   14        |
| 11        | 14        | Method1       |   267       |
| 42        | 1         | Method2       |   14,7      |
| 13        | 32        | Method1       |   100,2     |

Table B looks like this

 | TableB_ID | TravelExpenses | OperatingExpense |
 |  1        |  23            |   12             |
 |  1        | 234            |   24             |
 |  2        |  12            |    7             |
 |  1        | 432            |   12             |
 | 14        | 110            |   12             |

I am trying to create a measure Table (Table C) that looks like this:

 | TableC_ID |    TypeofCost   |     Amount       |
 |  1        | Method1         |   100,2          |
 |  2        | Method2         |   52             |
 |  3        | TravelExpenses  |    7             |
 |  4        | OperatingExpense|   12             |
 |  5        | Method3         |   12             |
 |  6        | OperatingExpense|   7              |
 |  7        | Method3         |   12             |

(the Amount results are to be Summed and Columns - Employee, Month, TypeofCost Grouped)

So I pretty much have to group not only by the PaymentMethod which I get from table A, but also insert new values in the group (TravelExpenses and OperatingExpense)

Can anybody give me any Idea about how this can be done in SQL ?

Here is what I have tried so far

SELECT PaymentMethod as TypeofCost
      ,Sum(ValuePayed) as Amount
FROM   TableA Left Outer Join TableB on TableA.TableB_ID = TableB.TableB_ID
GROUP  PaymentMethod

UNION 

SELECT 'TravelExpenses' as TypeofCost
       ,Sum(TableB.TravelExpenses) as Amount
FROM   TableA Left Outer Join TableB on TableA.TableB_ID = TableB.TableB_ID
GROUP  PaymentMethod

UNION

SELECT 'OperatingExpense' as TypeofCost
       ,Sum(TableB.OperatingExpense) as Amount
FROM   TableA Left Outer Join TableB on TableA.TableB_ID = TableB.TableB_ID
GROUP  PaymentMethod

Upvotes: 1

Views: 2749

Answers (1)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

It should be something like this:

Select
    row_number() OVER(ORDER BY TableB_ID) as 'TableC_ID',
    u.TypeofCost,
    u.Amount
  from (
        Select
            a.TableB_ID,
            a.PaymentMethod as 'TypeofCost',
            SUM(a.ValuePayed) as 'Amount'
        from
            Table_A as a
        group by a.TableB_ID, a.PaymentMethod

        union

        Select
            b1.TableB_ID,
            'TravelExpenses' as 'TypeofCost',
            SUM(b1.TravelExpenses) as 'Amount'
        from
            Table_B as b1
        group by b1.TableB_ID

        union

        Select
            b2.TableB_ID,
            'OperatingExpenses' as 'TypeofCost',
            SUM(b2.OperatingExpenses) as 'Amount'
        from
            Table_B as b2
        group by b2.TableB_ID
    ) as u

EDIT: Generate TableC_ID

Upvotes: 1

Related Questions