Matt McDermott Matt McDermott - 3 years ago 143
SQL Question

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

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

Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download