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:
- Rows from table 2 where the id, item or price don't match simultenously with a given row in table 1
- 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
SQL Function to Get Count of How Many Times String Appears in Column
What Is a Left Join in Postgresql
Oracle 10G Express Home Page Is Not Coming Up
Moving a Point Along a Path in SQL Server 2008
Get Latest Id from a Duplicate Records in a Table
Using Merge in SQL Server 2012 to Insert/Update Data
How to Concatenate Multiple Rows
How to Speed Up This Indexed View
"Similar Posts" Like Functionality Using Ms SQL Server
Sqlite3 (Or General SQL) Retrieve Nth Row of a Query Result
Ms SQL Server: Check to See If a User Can Execute a Stored Procedure