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

Question

I need help adding to my SQL statement. All of the Data is located in the same table. My current code is as follows,

SELECT Date,

       Val2,

       SNumber,

       SUM(isnull(L1,0) + isnull(L2,0) + isnull(L3,0)) As TotalR,

From Table1

Where Val2='Rep'

Group By

Value 2 has both "Store" and "Rep"

I have the total of L1,L2,and L3 coming in great (for Rep's).
what i need help with is how to get the

total of "Store" L1,L2, and L3 where
SNumber matches "Rep" and "Store"

Some of the "Reps" are in the same "store" and should have the same total.

Source Table                    

Date    Val2    Snumber L1  L2  L3

x   Store   1   11  5   4

x   Store   2   6   8   10

x   Rep1    1   5   2   1

x   Rep2    1   6   3   3

x   Rep3    2   2   1   5

x   Rep4    2   3   3   3

x   Rep5    2   1   4   2

Result

Date    Val2    Snumber TotalR  TotalS  

x   Rep1    1   8   20  

x   Rep2    1   12  20  

x   Rep3    2   8   24  

x   Rep4    2   9   24  

x   Rep5    2   7   24

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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'

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

Sign up