How to Sum Two Fields Within an SQL Query

How to SUM two fields within an SQL query

SUM is an aggregate function. It will calculate the total for each group. + is used for calculating two or more columns in a row.

Consider this example,

ID  VALUE1  VALUE2
===================
1   1       2
1   2       2
2   3       4
2   4       5

 

SELECT  ID, SUM(VALUE1), SUM(VALUE2)
FROM    tableName
GROUP   BY ID

will result

ID, SUM(VALUE1), SUM(VALUE2)
1   3           4
2   7           9

 

SELECT  ID, VALUE1 + VALUE2
FROM    TableName

will result

ID, VALUE1 + VALUE2
1   3
1   4
2   7
2   9

 

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID

will result

ID, SUM(VALUE1 + VALUE2)
1   7
2   16

Trying to sum two columns in SQL Server results in error message

Both of your percentage columns are a varchar, the + operator can concatenate strings together, and that is what you're seeing.

I would suggest that you use a CAST to a NUMERIC data type, especially as it seems that you have a fixed amount of spaces after the decimal.

Try the following:

SELECT 
    SUM(CAST(percent12 AS NUMERIC(10,2))+ CAST(percent21 AS NUMERIC(10,2))) AS total
FROM finalquery

Others have suggested that you CAST to a FLOAT, though that has known rounding errors (and known workarounds)

Float rounding error: SQL Server rounding Error, Giving different values

How to SUM multiple fields with condition

Either you mean:

1. You want to calculate a new column on each row which is the sum of columns 1 through 4 if those columns are 1 (which is the same as the count of columns which are 1), or
2. You want to calculate the sum of those columns but only when the row values of those columns are 1

For 1:

SELECT id, col1, col2, col3, col4
, CASE WHEN col1 = 1 THEN 1 ELSE 0 END
  + CASE WHEN col2 = 1 THEN 1 ELSE 0 END
  + CASE WHEN col3 = 1 THEN 1 ELSE 0 END
  + CASE WHEN col4 = 1 THEN 1 ELSE 0 END
AS row_count_of_ones
FROM table_name;

For 2:

SELECT SUM(
    CASE WHEN col1 = 1 THEN 1 ELSE 0 END
  + CASE WHEN col2 = 1 THEN 1 ELSE 0 END
  + CASE WHEN col3 = 1 THEN 1 ELSE 0 END
  + CASE WHEN col4 = 1 THEN 1 ELSE 0 END
     ) AS count_of_ones
FROM table_name;

Total Sum of two columns and show in the third Column

You can use comuted column for this:

CREATE TABLE [dbo].[Test](
    [a] [INT] NULL,
    [b] [INT] NULL,
    [c]  AS ([a]+[b])
) ON [PRIMARY]

GO

INSERT INTO dbo.Test
        ( a, b )
VALUES  ( 1, -- a - int
          2  -- b - int
          )


SELECT * FROM dbo.Test 

Results:
a   b   c
1   2   3

SQL Query to get the sum of two columns rest being the same value

A GROUP BY should resolve your issue. Only issue is for your RATE value. It depends what you wants in your output. If all Rows contain the same value for a GROUP, You can simply calculate AVG or MIN or MAX and all these will return you the same result. But, if there are different value, you have to define your method what output you wants.

SELECTeffective_date,
person_number Employee_number,
Name,
CLASSIFICATIONNAME,
Element,
SUM(resultvalue)  CURRENTAMOUNT,
SUM(HOURS),
AVG(RATE), -- Avg should keep the same value if rate is same for the group
(Count(resultvalue)) COUNT_VALUE,
(Count( resultvalue) * hours) HOURS_VALUE
from 
-- Use standard joining
per_payroll_table pay 
INNER JOIN per_all_people people
ON pay.person_id  = people.person_id
GROUP BY ffective_date,
person_number Employee_number,
Name,
CLASSIFICATIONNAME,
Element

Sum two columns in SQL and optimise SQL query

You can use CROSS APPLY or OUTER APPLY instead of a correlated subquery.

Then you can re-use the values.

select c.pinned, c.createddate
, c.discussionid
, ca1.content
, ca2.childcontent
, (ca1.content + ca2.childcontent) AS sumdata
FROM comments c
CROSS APPLY 
(
 SELECT CASE
        WHEN c.isdeleted = 1 OR c.approved = 0 THEN 0
        ELSE 1
        END AS content
) ca1
CROSS APPLY 
(
 SELECT COUNT(c2.content) AS childcontent
 FROM comments c2
 WHERE c2.parentcommentid = c.id
   AND c2.isdeleted = 0
   AND c2.approved = 1
) ca2
WHERE c.discussionid = '257943' 
  AND c.parentcommentid IS NULL 
ORDER BY 
  c.pinned DESC,
  c.createddate;

SQL query to sum multiple columns across multiple rows

All you seem to want is a sum(s) with a group by id

DROP TABLE IF EXISTS T;

CREATE TABLE T
(ID INT,    Value1 DECIMAL(10,2),   Value2 DECIMAL(10,2));
INSERT INTO T VALUES
(1, 1033.90 ,0.00),
(1, 0.00       ,1033.90),
(1, 1181.60 ,0.00);


SELECT ID , SUM(VALUE1) - SUM(VALUE2) AS TOT
FROM T
GROUP BY ID;

+------+---------+
| ID   | TOT     |
+------+---------+
|    1 | 1181.60 |
+------+---------+
1 row in set (0.001 sec)

and the group by is unnecessary if you have only 1 id. If you want a running total which where I think Lamu is coming from then you would need some way of ordering events

how to sum two columns from different tables MySQL

First use UNION ALL to get all the rows from the 2 tables that you want and then aggregate:

SELECT session_id, SUM(points) AS total_points
FROM (
  SELECT session_id, spent_points AS points
  FROM session_details 
  WHERE session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") 
  UNION ALL
  SELECT session_id, price_points
  FROM template_sales 
  WHERE session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") 
) t
GROUP BY session_id

SQL query to sum separate columns with separate Where conditions

Use conditional aggregation:

select name, sum(case when value1 > 10 then value1 end),
       sum(case when value2 > 20 then value2 end)
from t
group by name;

---

Related Topics