SQL Sum Field When Column Values Match

SQL sum field when column values match

SELECT SUM(Value) AS 'Total', [Product Name]
FROM TableX
GROUP BY [Product Name]

SQL Fiddle Example

sql sum some column when all other columns match

Try to add aliases:

SELECT 
T.catalogid,
sum(T.numitems) as numitems,
sum(T.ignoreditesm) as ignoreditesm
FROM ##temporderstable T
GROUP BY
T.catalogid, T.supplierid, T.cname, T.cprice,
T.cstock, T.ccode, T.minstock, T.pother4

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'

MYSQL - sum values of rows from one table based on criteria from another

it is the same, you need to join the tables

SELECT SUM(cost) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id WHERE colour='blue';

| SUM(cost) |
| --------: |
| 80 |

db<>fiddle here

How to sum a column where another column is equal to other table

Assuming there is a unique identifier field in Main.

Consider:

Query1

SELECT ID, "Ac1" AS Src, Ac1 AS Act, Amt FROM Main
UNION SELECT ID, "Ac2", Ac2, Amt*-1 FROM Main;

Query2

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN Query1 ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

All in one

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN
(SELECT Ac1 AS Act, Amt FROM Main
UNION SELECT Ac2, Amt*-1 FROM Main) AS Query1
ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

comparing sum of 2 column values in one table with another column value in second table sql server

If I haven't misunderstood your requirements, this should do it:

 SELECT T2.ID, T2.ITEM,T2.SUMPRICE FROM 
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table1 GROUP BY ID, ITEM) AS T1
INNER JOIN
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table2 GROUP BY ID, ITEM) AS T2
ON T1.ID = T2.id AND T1.item = T2.item WHERE T1.SUMPRICE <> T2.SUMPRICE

If your 3rd table is already created you could just use an INSERT INTO SELECT statement. Otherwise you could use a SELECT INTO like this:

SELECT T2.ID, T2.ITEM,T2.SUMPRICE as price into table3 FROM 
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table1 GROUP BY ID, ITEM) AS
T1
INNER JOIN
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table2 GROUP BY ID, ITEM) AS
T2
ON T1.ID = T2.id AND T1.item = T2.item WHERE T1.SUMPRICE <> T2.SUMPRICE

Hope it helps!

EDIT 1
In the case that you want to get all the unmatched rows, that is:

  1. Rows from table 2 where the id, item or price don't match simultenously with a given row in table 1
  2. Rows from table 2 where none of their columns match simultenously with a given row in table 1

You could use the EXCEPT statement, for example:

(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table2 GROUP BY ID, ITEM) 
EXCEPT
(SELECT ID, ITEM, SUM(PRICE) AS SUMPRICE FROM table1 GROUP BY ID, ITEM)

This returns:

ID   ITEM                 SUMPRICE
---- -------------------- -----------
10 book 50


Related Topics



Leave a reply



Submit