2

Suppose that I have a table named tblTemp which has this data:

|  ID | AMOUNT |
----------------
|   1 |     10 |
| 1-1 |     20 |
| 1-2 |     30 |
| 1-3 |     40 |
|   2 |     50 |
|   3 |     60 |
|   4 |     70 |
| 4-1 |     80 |
|   5 |     90 |
|   6 |    100 |

ID will be formatted as X (without a dash) if it's only one ID or (X-Y) if it's a new ID (Y) is child of (X).

I want to add a new column (Total Amount) to output as below:

|  ID | AMOUNT |   Total Amount |
---------------------------------
|   1 |     10 |        100     |
| 1-1 |     20 |        100     |
| 1-2 |     30 |        100     |
| 1-3 |     40 |        100     |
|   2 |     50 |         50     |
|   3 |     60 |         60     |
|   4 |     70 |        150     |
| 4-1 |     80 |        150     |
|   5 |     90 |         90     |
|   6 |    100 |        100     |

The "Total Amount" column is calculated as the sum of the amounts column for the same X ID.

In order to get the parent ID (X), I use the following SQL:

SELECT
  ID, SUBSTRING (ID, 1,
                 IIF (CHARINDEX('-', ID) = 0,
                        len(ID),
                        CHARINDEX('-', ID) - 1)
  ), Amount
FROM
  tblTemp

How can I write a query like this in SQL Server 2012?

You can use sqlfiddle here to test it.

8

What you're looking for is called a window function. It's similar to grouping, but it allows you to perform aggregate calculations on sets of records without actually grouping. Searching for "window function" or "over clause" should get you lots of helpful reading material.

select
  [ID]
  ,Amount
  ,[Total Amount] = sum(Amount) over(
    partition by left([ID],charindex('-',ID + '-') - 1)
    )
  from tblTemp
1
  • 1
    Pretty creative by adding '-' at the end. – Travis Jun 4 '13 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.