SQL Server SUM() and group by with sub same name

bq9c1y66 于 11个月前发布在其他

关注(0)|答案(3)|浏览(98)

I've this data in my table
| Col1 | Col2 |
| ------------ | ------------ |
| master-milk | 100 |
| submaster-milk | 200 |
| master-coffee | 10 |
| submaster-coffee | 20 |

And I need to calculate SUM() of my milk and coffee Sum(milk) = 300 and sum(coffee) = 30

Select col1,sum(col1) as total from Table  
group by col1

sql-server

来源：https://stackoverflow.com/questions/77387347/sum-and-group-by-with-sub-same-name

3条答案

按热度按时间

vs3odd8k1#

You can use a common table expression (CTE) to first categorize the rows and then perform the SUM operation.

;WITH CTEData AS (
    SELECT 
        CASE 
            WHEN Col1 LIKE '%milk' THEN 'milk'
            WHEN Col1 LIKE '%coffee' THEN 'coffee'
            ELSE 'other'
        END AS DrinkType,
        Col2
    FROM YourTable
)
SELECT DrinkType, SUM(Col2) AS Total
FROM CTEData GROUP BY DrinkType;

赞(0）回复(0）举报 11个月前

cgvd09ve2#

You can use SUBSTRING to extract milk/coffee from the string :

SELECT SUBSTRING(Col1, CHARINDEX('-', Col1)+1, LEN(Col1)) as Col1, sum(Col2) as Total
FROM mytable
GROUP BY SUBSTRING(Col1, CHARINDEX('-', Col1)+1, LEN(Col1))

赞(0）回复(0）举报 11个月前

pcww981p3#

Another solution :

SELECT 
    CASE 
        WHEN Col1 LIKE '%milk' THEN 'milk'
        WHEN Col1 LIKE '%coffee' THEN 'coffee'
        ELSE 'other'
    END AS DrinkType,
    SUM(Col2) AS Total
FROM YourTable
GROUP BY  CASE 
        WHEN Col1 LIKE '%milk' THEN 'milk'
        WHEN Col1 LIKE '%coffee' THEN 'coffee'
        ELSE 'other'
    END

赞(0）回复(0）举报 11个月前

我来回答

SQL Server SUM() and group by with sub same name

3条答案

相关问题

热门标签

最新问答