0

I have table look like the following

Col1 Col2(nvarchar) Col3
1 2/4 100
1 ​ 2/4 200
2 ​ 1/3 140
3 ​ 2/3 120
4 3/4 200

Result would be: The sum of column 3 group by column 1 + column 3 of 2 and column 3 of 4 (excluding the / from col 2) For example, 1st row will be 100 + 200 + 140 + 200 = 640. Result would be like the following:

Col1 Sum
1 640
2 560
3 380
4 520

How can I make or what would be the query in sql server to get such resultant one?

Chinmoy Bhowmik
  • 59
  • 1
  • 1
  • 5
  • 1
    I don't follow. Why are `140` and `200` in the "group" when the first 2 rows have a value of `1` and `2/4` and no other rows do. Take the time to better explain the logic here. – Thom A Nov 10 '21 at 17:13
  • *"The sum of column 3 group by column 1 + column 3 of 2 and column 3 of 4 (excluding the / from col 2)"* I have literally no idea what this means. Please explain better – Charlieface Nov 10 '21 at 20:50
  • column 3 group by column 1 (for 1 it would be 100 + 200 = 300). from column 2 we will get 2/4 now eliminating '/' we get 2 and 4. Now we will go to the records for 2 and we will find it 140 and again for 4 we will get 200. My total resultant for 2 and 4 would be 300 + 140 + 200 = 640. – Chinmoy Bhowmik Nov 11 '21 at 04:16

1 Answers1

1

Try something like this:

DECLARE @data table ( Col1 int, Col2 nvarchar(3), Col3 int );
INSERT INTO @data VALUES
    ( 1, '2/4', 100 ),
    ( 1, '2/4', 200 ),
    ( 2, '1/3', 140 ),
    ( 3, '2/3', 120 ),
    ( 4, '3/4', 200 );

;WITH cte AS (
    SELECT
        Col1, Col2, SUM ( Col3 ) AS Col3Sum
    FROM @data AS d
    GROUP BY
        Col1, Col2
)
SELECT
    Col1, ( Col3Sum + Col2MatchesSum ) AS [Sum]
FROM cte
OUTER APPLY (

    -- get the sum of rows matching Col2 delimited values.
    SELECT SUM ( Col3 ) AS Col2MatchesSum FROM @data WHERE Col1 IN (
        SELECT CAST ( [value] AS int ) FROM STRING_SPLIT ( cte.Col2, '/' )
    )

) AS n
ORDER BY
    Col1;

RETURNS

+------+-----+
| Col1 | Sum |
+------+-----+
|    1 | 640 |
|    2 | 560 |
|    3 | 380 |
|    4 | 520 |
+------+-----+
critical_error
  • 6,306
  • 3
  • 14
  • 16
  • It really works fine. Thanks much boss... – Chinmoy Bhowmik Nov 11 '21 at 05:41
  • What will be the query without using STRING_SPLIT function? In fact my database compatibility level is below 130 and thus I can not use this built in functions. I have no option to change my database compatibility as well. So It would be great if you give me the same query without using STRING_SPLIT() . – Chinmoy Bhowmik Nov 14 '21 at 07:43
  • I actually just answered that. Check out my answer here: https://stackoverflow.com/questions/69925112/alternative-of-string-split-function-on-lower-compatibility-level/69929811#69929811 – critical_error Nov 14 '21 at 23:03