CODEWITHSUNDEEP
sqlsql-server-2008
mateskabe
mateskabe

Reputation: 289

Sum records and add note what was summed up in sql

I have a simple table looks like this one:

company_Id    user_Id    price   sub_price
   123456      11111      200      NULL
   123456      11111      500      NULL
   456789      22222      300      NULL

And I want to consolidate records which has count(*) >= 2 into one row by summing up the price but with note what was summed up in column sub_price. Desired output should look like this one:

 company_Id    user_Id    price   sub_price
   123456      11111      700      200,500
   456789      22222      300      300

Is there any simple approach how to achieve desired output? Many thanks for your help in advance.

Upvotes: 0

Views: 42

Answers (2)

Valerica
Valerica

Reputation: 1630

This is one possible solution; More info about concatenating multiple rows into single row you can find here

DECALRE @tbl AS table (
    company_Id int
    ,user_Id int
    ,price int
    ,sub_price varchar(25)
)

INSERT INTO @tbl values (123456, 11111, 200, NULL)
INSERT INTO @tbl values (123456, 11111, 500, NULL)
INSERT INTO @tbl values (456789, 22222, 300, NULL)


SELECT
    company_Id
    ,user_Id
    ,SUM(price) AS price
    ,STUFF(
        (SELECT ',' + cast(price as varchar)
        FROM @tbl
        WHERE company_Id = a.company_id
            AND user_Id = a.user_Id 
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') AS sub_price
FROM @tbl a
GROUP BY company_Id, user_Id

Upvotes: 0

Markus Winand
Markus Winand

Reputation: 8706

You can use listagg to turn the elements of a group into a string:

SELECT ...
     , LISTAGG(price, ',') WITHIN GROUP (ORDER BY price) sub_price
  FROM ...

Although listagg is SQL standard, it is not yet supported by all databases. However, most database offer similar functionality by a different name—e.g. string_agg in PostgreSQL and SQL Sever (since 2017) or group_concat in MySQL.

More info: http://modern-sql.com/feature/listagg (also showing alternatives if listagg is not supported)

Upvotes: 1

Related Questions