Select Query for Sum and group it-CodePudding

I am confused to getting the merge the item column. Please help me to do this.

CodePudding user response：

You can use this for SQL SERVER < 2017

SELECT 
Supplier
,Items = STUFF((
        SELECT ' | '   t2.Item
        FROM TestTable t2
        WHERE t.Supplier = t2.Supplier
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
,SUM(Qty) Qty
,SUM(ST) ST
,SUM(Amt) Amt
FROM TestTable t
GROUP BY Supplier

CodePudding user response：

Solution in PostgreSQL. Please note distinct item.

select string_agg(distinct item, '|') items, supplier, 
       sum(qty) qty, sum(st) st, sum(amt) amt
from the_table 
group by supplier;

Edit
My answer above is not relevant anymore after sql-server DB was tagged.

CodePudding user response：

Solution in Oracle/DB2/Snowflake SQL:
(The LISTAGG function is ANSI/SQL:2016 compliant, but not generally supported in every recent RDBMS version)

SELECT
 LISTAGG(Item, '|') WITHIN GROUP (ORDER BY Qty) AS Item, 
 Supplier, 
 SUM(Qty) AS Qty, 
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable 
GROUP BY Supplier
ORDER BY Supplier

Solution for MS Sql Server 2014 :

SELECT
 STUFF((select '|'  t2.Item
        from yourtable t2
        where t2.Supplier = t.Supplier
        order by t2.Qty
        for xml path ('')),1,1,'') AS Item,
 Supplier, 
 SUM(Qty) AS Qty,
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable t
GROUP BY Supplier
ORDER BY Supplier

Solution for MS Sql Server 2017 using STRING_AGG :

SELECT
 STRING_AGG(Item, '|') WITHIN GROUP (ORDER BY Qty) AS Item,
 Supplier, 
 SUM(Qty) AS Qty,
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable t
GROUP BY Supplier
ORDER BY Supplier

CodePudding user response：

You should really read the documentation about SQL. Nevertheless in MySQL you could do:

SELECT GROUP_CONCAT(Item SEPARATOR ' | '), SUM(Qty), SUM(ST), SUM(Amt) FROM <supplier> GROUP BY Supplier;

Where <supplier> should be replaced by the name of your table.