Nursultan Aidarkulov Nursultan Aidarkulov - 6 months ago 8
SQL Question

How to replace all values in grouped column except first row

I have table like this:

ID Region CreatedDate Value
--------------------------------
1 USA 2016-01-01 5
2 USA 2016-02-02 10
3 Canada 2016-02-02 2
4 USA 2016-02-03 7
5 Canada 2016-03-03 3
6 Canada 2016-03-04 10
7 USA 2016-03-04 1
8 Cuba 2016-01-01 4


I need to sum column
Value
grouped by
Region
and
CreatedDate
by year and month. The result will be

Region Year Month SumOfValue
--------------------------------
USA 2016 1 5
USA 2016 2 17
USA 2016 3 1
Canada 2016 2 2
Canada 2016 3 13
Cuba 2016 1 4


BUT I want to replace all repeated values in column
Region
with empty string except first met row. The finish result must be:

Region Year Month SumOfValue
--------------------------------
USA 2016 1 5
2016 2 17
2016 3 1
Canada 2016 2 2
2016 3 13
Cuba 2016 1 4


Thank you for a solution. It will be advantage if solution will replace also in column
Year

Answer

You need to use SUM and GROUP BY to get the SumOfValue. For the formatting, you can use ROW_NUMBER:

WITH Cte AS(
    SELECT
        Region,
        [Year]  = YEAR(CreatedDate),
        [Month] = MONTH(CreatedDate),
        SumOfValue = SUM(Value),
        Rn = ROW_NUMBER() OVER(PARTITION BY Region ORDER BY YEAR(CreatedDate), MONTH(CreatedDate))
    FROM #tbl
    GROUP BY
        Region, YEAR(CreatedDate), MONTH(CreatedDate)
)
SELECT
    Region = CASE WHEN Rn = 1 THEN c.Region ELSE '' END,
    [Year],
    [Month],
    SumOfValue
FROM Cte c
ORDER BY
    c.Region, Rn

ONLINE DEMO

Although this can be done in TSQL, I suggest you do the formatting on the application side.