sql - 如何在新列中使用 SUM 的输出？

Question

我的 table 是这样的:

Ride_distance    Ride_type
100              A         
50               B        
25               B       
25               C           
10               C          
40               A         
...              ...        

现在我想对每种类型的所有距离求和并像这样显示它们:

Tot_Type_A  Tot_Type_B  Tot_Type_C
140         75          35

---

我该怎么做？

Answer 1

从分组开始:

SELECT SUM(Ride_distance),Ride_type FROM rides GROUP BY Ride_type

如果您必须将它们放在同一行，请使用 pivot :

SELECT 'TotalRide' as Total_Ride_Length, [A], [B], [C]
FROM
(SELECT Ride_distance, Ride_type FROM rides) AS SourceTable
PIVOT (
    SUM(Ride_distance) FOR Ride_type IN ([A], [B], [C])
) AS PivotTable;