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

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2017-10-01 07:00:33 +00:00
Commented Oct 1, 2017 at 7:00 — user330315
– user330315, Commented Oct 1, 2017 at 7:00

Nazim · Accepted Answer · 2017-10-01 06:58:33Z

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'

edited Oct 1, 2017 at 6:58

answered Sep 30, 2017 at 23:48

Nazim

6392 gold badges11 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt McDermott Over a year ago

Awesome! Defiantly a step in the right direction. any recommendation on removing duplicate entries?

Matt McDermott Over a year ago

I am getting two entries for TotalS. The higher value of the two is the correct amount. I need to remove the row with the lower amount.

Nazim Over a year ago

Edited my answer with ADDITION, check the above solution. Is that what you want to achieve?

Matt McDermott Over a year ago

The code looks like it will give me the result! Ill have to try it tomorrow! Maybe this is for a seperate question but the end result is to devide TotalR by TotalS to get a contribution %. I cant thabk you enough for the support!

SE1986 · Accepted Answer · 2017-09-30 23:56:39Z

0

SELECT  b.Date,
        b.Val2,
        b.Snumber,
        L1+L2+L3 AS TotalR,
        SUM(L1+L2+L3) OVER (PARTITION BY Snumber)
FROM    (
            SELECT  Val2
            FROM    Table1
            WHERE   Val2 LIKE 'Rep%'
        ) a
        JOIN Table1 b
            ON a.Val2 = b.Val2

answered Sep 30, 2017 at 23:56

SE1986

2,7901 gold badge15 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related