SQLite中如何对查询的别名列Profit、ST求和？求正确语句

Hey there! I get it—you want to calculate the total sum of the Profit column (aliased as sumProfit) and ST column (aliased as sumST) alongside your existing SQL query results. Let's break this down, and first fix a small critical issue in your original query that would throw an error.

First off, your original query has a join order problem: you're referencing C.Match_ID in the first join with tp before you've actually joined the Computations as C table. That'll trigger an "invalid object name" error. We'll fix that by reordering the joins properly first.

Option 1: Keep individual match details + add a grand total row #

If you want to retain every match's data plus a final row showing the total sums, use GROUP BY ... WITH ROLLUP. This preserves all your original detail rows and adds a summary row at the end.

Here's the adjusted query:

SELECT 
    CASE WHEN GROUPING(C.Match_ID) = 1 THEN 'Total' ELSE CAST(C.Match_ID AS VARCHAR) END AS Match_ID,
    CASE WHEN GROUPING(M.Match_Date) = 1 THEN '' ELSE CAST(M.Match_Date AS VARCHAR) END AS Match_Date,
    CASE WHEN GROUPING(T1.TeamName) = 1 THEN '' ELSE T1.TeamName END AS HomeTeam,
    CASE WHEN GROUPING(T2.TeamName) = 1 THEN '' ELSE T2.TeamName END AS AwayTeam,
    CASE WHEN GROUPING(L.League_MyName) = 1 THEN '' ELSE L.League_MyName END AS League_MyName,
    CASE WHEN GROUPING(S.Season_Year) = 1 THEN '' ELSE CAST(S.Season_Year AS VARCHAR) END AS Season_Year,
    CASE WHEN GROUPING(M.algo) = 1 THEN '' ELSE M.algo END AS algo,
    ROUND((tp.Home*100),3) as TOP,
    CASE WHEN ROUND((tp.Home*100),3)=0 THEN 0 ELSE ROUND(1/(tp.Home),3) END as TOd,
    LW.Home as LW,
    CASE WHEN Pr.Home=0 THEN 0.0 ELSE ROUND((tp2.Home*100),3) END as TV,
    Pr.Home as BOd,
    CASE WHEN Pr.Home=0 THEN 0.0 ELSE ROUND((1/Pr.Home)*100,3) END as BOP,
    ROUND(SUM(CASE WHEN Pr.Home=0 THEN 0.0 WHEN Pr.Home<2 THEN 100.0 ELSE 100.0/(Pr.Home-1) END),2) as ST,
    ROUND(SUM(CASE 
        WHEN Pr.Home =0 THEN 0.0 
        WHEN LW.Home = 'W' THEN (CASE WHEN Pr.Home<2 THEN 100.0 ELSE 100.0/(Pr.Home-1) END) * (Pr.Home-1) 
        WHEN LW.Home = 'DNB' THEN 0.0 
        ELSE -(CASE WHEN Pr.Home<2 THEN 100.0 ELSE 100.0/(Pr.Home-1) END) 
    END),2) as Profit,
    -- Total sums
    ROUND(SUM(CASE 
        WHEN Pr.Home =0 THEN 0.0 
        WHEN LW.Home = 'W' THEN (CASE WHEN Pr.Home<2 THEN 100.0 ELSE 100.0/(Pr.Home-1) END) * (Pr.Home-1) 
        WHEN LW.Home = 'DNB' THEN 0.0 
        ELSE -(CASE WHEN Pr.Home<2 THEN 100.0 ELSE 100.0/(Pr.Home-1) END) 
    END),2) as sumProfit,
    ROUND(SUM(CASE WHEN Pr.Home=0 THEN 0.0 WHEN Pr.Home<2 THEN 100.0 ELSE 100.0/(Pr.Home-1) END),2) as sumST
FROM Matches as M
INNER JOIN Computations as C ON C.Match_ID = M.Match_ID
INNER JOIN (SELECT Home, Match_ID FROM Computations WHERE Computation_Type_ID = 1) as tp ON tp.Match_ID = C.Match_ID
INNER JOIN (SELECT Home, Match_ID FROM Computations WHERE Computation_Type_ID = 2) as tp2 ON tp2.Match_ID = C.Match_ID
INNER JOIN Leagues as L ON L.Real_League_ID = M.Real_League_ID
INNER JOIN Season as S ON S.Season_ID = M.Season_ID
INNER JOIN Teams as T1 ON T1.Team_ID = M.Home_TeamID
INNER JOIN Teams as T2 ON T2.Team_ID = M.Away_TeamID
INNER JOIN LostWon as LW ON LW.Match_ID=C.Match_ID
INNER JOIN Prices as Pr ON Pr.Match_ID=C.Match_ID
WHERE M.Real_League_ID=44
GROUP BY C.Match_ID, M.Match_Date, T1.TeamName, T2.TeamName, L.League_MyName, S.Season_Year, M.algo, tp.Home, LW.Home, Pr.Home, tp2.Home WITH ROLLUP
HAVING GROUPING(C.Match_ID) = 0 OR (GROUPING(C.Match_ID) = 1 AND GROUPING(M.Match_Date) = 1)

Quick notes on this version:

• We reordered the joins so Computations as C is joined first (fixing the original error).
• GROUPING(C.Match_ID) = 1 flags the grand total row—we use CASE statements to make this row readable (show "Total" for Match_ID, empty strings for other detail columns).
• Wrapped the ST and Profit calculations in SUM() so they aggregate correctly in the total row.
• The HAVING clause filters out partial subtotal rows, leaving only individual matches and the grand total.

Option 2: Only get the total sums (no detail rows) #

If you don't need individual match data and just want the total sumProfit and sumST, wrap your fixed original query as a subquery and aggregate from there:

SELECT 
    ROUND(SUM(Profit), 2) as sumProfit,
    ROUND(SUM(ST), 2) as sumST
FROM (
    SELECT 
        C.Match_ID,
        CASE WHEN Pr.Home=0 THEN 0.0 WHEN Pr.Home<2 THEN 100.0 ELSE ROUND(100.0/(Pr.Home-1),2) END as ST,
        CASE 
            WHEN Pr.Home =0 THEN 0.0 
            WHEN LW.Home = 'W' THEN ROUND((CASE WHEN Pr.Home<2 THEN 100.0 ELSE ROUND(100.0/(Pr.Home-1),2) END) * (Pr.Home-1),2) 
            WHEN LW.Home = 'DNB' THEN 0.0 
            ELSE -(CASE WHEN Pr.Home<2 THEN 100.0 ELSE ROUND(100.0/(Pr.Home-1),2) END) 
        END as Profit 
    FROM Matches as M
    INNER JOIN Computations as C ON C.Match_ID = M.Match_ID
    INNER JOIN (SELECT Home, Match_ID FROM Computations WHERE Computation_Type_ID = 1) as tp ON tp.Match_ID = C.Match_ID
    INNER JOIN (SELECT Home, Match_ID FROM Computations WHERE Computation_Type_ID = 2) as tp2 ON tp2.Match_ID = C.Match_ID
    INNER JOIN Leagues as L ON L.Real_League_ID = M.Real_League_ID
    INNER JOIN Season as S ON S.Season_ID = M.Season_ID
    INNER JOIN Teams as T1 ON T1.Team_ID = M.Home_TeamID
    INNER JOIN Teams as T2 ON T2.Team_ID = M.Away_TeamID
    INNER JOIN LostWon as LW ON LW.Match_ID=C.Match_ID
    INNER JOIN Prices as Pr ON Pr.Match_ID=C.Match_ID
    WHERE M.Real_League_ID=44
    GROUP BY C.Match_ID, Pr.Home, LW.Home, tp.Home, tp2.Home
) AS match_details

This version:

• Runs your core logic in the inner subquery to get ST and Profit per match.
• The outer query sums those values to return only the grand totals.

内容的提问来源于stack exchange，提问作者Chadi