Select Sum and Multiple Columns in 1 Select Statement

Select Sum and multiple columns in 1 select statement

try add GROUP BY

SELECT sum(a) as car,b,c FROM toys 
GROUP BY b, c

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

How to select multiple columns, sum one column and group by multiple columns

are you looking for this? :

select FORMAT(d.date_start, 'yyyy-MM') date_start
     , count(distinct user_id) total
from planner d
group by FORMAT(date_start, 'yyyy-MM')

SQL sum different columns into one statement

In your case, you can do:

select sum(column1) + sum(column2) + sum(column3)
from table1 t;

Because each column has at least one value, the individual sums will not be NULL, so this will give the expected value.

To be safe, you could use coalesce():

select coalesce(sum(column1), 0) + coalesce(sum(column2), 0) + coalesce(sum(column3), 0)
from table1 t;

SQL SUM multiple columns and match based on multiple values in one column

You need to use inner join with the subquery of the same table Table1.

Here below I prepared scripts for creating and inserting sample data you specified:

CREATE TABLE Table1 (
    Date DateTime,
    Val2 Varchar(5),
    SNumber Smallint,
    L1 Smallint,
    L2 Smallint,
    L3 Smallint
)

Run this insert script to create data on the table.

INSERT INTO Table1
VALUES(CAST(GETDATE() AS DATE), 'Store', 1, 11, 5, 4),
(CAST(GETDATE() AS DATE), 'Store', 2, 6, 8, 10),
(CAST(GETDATE() AS DATE), 'Rep1', 1, 5, 2, 1),
(CAST(GETDATE() AS DATE), 'Rep2', 1, 6, 3, 3),
(CAST(GETDATE() AS DATE), 'Rep3', 2, 2, 1, 5),
(CAST(GETDATE() AS DATE), 'Rep4', 2, 3, 3, 3),
(CAST(GETDATE() AS DATE), 'Rep5', 2, 1, 4, 2)

And on the created table if you run the below script you will get the expected result:

SELECT Date, Val2, Table1.SNumber, (L1 + L2 + L3) AS TotalR, S.TotalS
FROM Table1(NOLOCK)
  JOIN (
      SELECT SNumber, L1 + L2 + L3 AS TotalS
      FROM Table1(NOLOCK)
      WHERE Val2 = 'Store'
   ) S
   ON Table1.SNumber = S.SNumber
 WHERE Val2 <> 'Store'

Try on SQL Fiddle

ADDITION:
The below script lists repos for the store with the maximum TotalS:

SELECT Date, Val2, Table1.SNumber, (L1 + L2 + L3) AS TotalR, S.TotalS
FROM Table1(NOLOCK)
  JOIN (
      SELECT TOP 1 SNumber, L1 + L2 + L3 AS TotalS
      FROM Table1(NOLOCK)
      WHERE Val2 = 'Store'
      ORDER BY TotalS DESC
   ) S
   ON Table1.SNumber = S.SNumber
 WHERE Val2 <> 'Store'

How to sum the values of two columns and have it in a new one?

If you are trying to get the the sum of the two columns, you just need ton handle the null values properly.

SELECT COALESCE(speciality_count, 0) + COALESCE(Italian_count, 0)
FROM table_name 

---

Related Topics