数据表中求和值的总和计算：SQL实现需求问询

Let's break down what's going wrong with your current query first:
Your existing code select *,(item 1 + item 2 ) + (item 1 + item 2 ) as ItemS from MyTable is calculating twice the sum of item_1 and item_2 for each individual row (so the first row would return 4, the second 6), not the total sum across all rows.

To get the total sum of item_1 + item_2 (which is 5 in your case) as you want, here are two common approaches depending on your desired output format:

Option 1: Show Total Sum on Every Row #

If you want the total sum displayed alongside each row of original data, use a window function (supported in most modern databases like PostgreSQL, MySQL 8+, SQL Server, etc.):

SELECT 
    item_1,
    item_2,
    item_3,
    item_4,
    SUM(item_1 + item_2) OVER () AS ItemS
FROM MyTable;

This query calculates the global sum of item_1 + item_2 across all rows and repeats it in every row's ItemS column.

Option 2: Show Total Sum as a Separate Final Row #

If you want the original rows first, followed by a single row with the total sum (matching your expected output), use UNION ALL to combine your original data with an aggregate sum query:

-- Fetch original rows with empty ItemS column
SELECT 
    item_1,
    item_2,
    item_3,
    item_4,
    NULL AS ItemS
FROM MyTable

UNION ALL

-- Calculate total sum, leave other columns empty
SELECT 
    NULL,
    NULL,
    NULL,
    NULL,
    SUM(item_1 + item_2) AS ItemS
FROM MyTable;

Note: If you prefer empty strings instead of NULL in the first part's ItemS column, replace NULL with '' (works in most databases; adjust if using a niche system).

For Older Databases Without Window Functions #

If you're using an older database that doesn't support window functions (e.g., MySQL 5.x), use a subquery to fetch the total sum:

SELECT 
    item_1,
    item_2,
    item_3,
    item_4,
    (SELECT SUM(item_1 + item_2) FROM MyTable) AS ItemS
FROM MyTable;

This achieves the same result as Option 1, just using a subquery instead of a window function.

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