Best way to write a sum query.

Question

Post reply

Best way to write a sum query.

Kelvin Phayre

Ten Centuries

Points: 1358
More actions
November 25, 2010 at 4:06 am

#227195

I have two tables which I want to return the sum on a value.
Table 1
wh,item,stock
01,P1,1
02,P1,1
03,P1,1
table 2
wh,item,date,move
01,P1,01/01/2010,100
02,P1,02/01/2010,200
02,P1,03/01/2010,50
02,P1,04/01/2010,100
03,P1,05/01/2010,20
I want a query that will sum(stock) & sum(move) for grouped by item
so
select t1.item,sum(stock),sum(move) from table1 t1 join table2 t2 on t1.wh=t2.wh and t1.item=t2.item
group by t1.item
I want output to be
item column2 column3
------- ---------- ----------
P1 3 470
...
but will get !!!!!
item column2 column3
------- ---------- ----------
P1 5 470
What is the most elegant (simple) way of doing this kind of query?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

How does this look?

FYI - I have created the table variables in SQL 2008 and have hence used row constructors.

DECLARE @Table1 TABLE (wh INT,item NVARCHAR(10),stock INT)

INSERT INTO @Table1 VALUES (01,'P1',1),

(02,'P1',1),

(03,'P1',1)

DECLARE @Table2 TABLE (wh INT,item NVARCHAR(10),StockDate DATE, Movement INT)

INSERT INTO @Table2 VALUES (01,'P1','01/01/2010',100),

(02,'P1','02/01/2010',200),

(02,'P1','03/01/2010',50),

(02,'P1','04/01/2010',100),

(03,'P1','05/01/2010',20)

;WITH MyCTE (Item, WH, Movement) AS (SELECT t2.item,t2.wh,SUM(t2.Movement)

FROM @Table2 t2

GROUP BY t2.item,t2.wh)

SELECT t1.item, SUM(t1.Stock), SUM(MyCTE.Movement)

FROM MyCTE

INNER JOIN @Table1 t1 ON MyCTE.Item = t1.item AND MyCTE.WH = t1.wh

GROUP BY t1.item

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Kelvin Phayre Ten Centuries Points: 1358 More actions · Answer 2

Nice one. Never seen CTE's before so will have a read up.

Thanks.

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

You are welcome!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Kelvin Phayre Ten Centuries Points: 1358 More actions · Answer 4

One last thing.

From your data.

INSERT INTO @Table2 VALUES (01,'P1','01/01/2010',100),

(02,'P1','02/01/2010',200),

(02,'P1','03/01/2010',50),

(02,'P1','04/01/2010',100),

(03,'P1','05/01/2010',20)

How do I get the following from Table2?

I want the last date in a column and the last movement quantity?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- Make a couple of sample tables

CREATE TABLE #Table1 (wh CHAR(2), item CHAR(2), stock INT)

INSERT INTO #Table1 (wh, item, stock)

SELECT '01','P1',1 UNION ALL

SELECT '02','P1',1 UNION ALL

SELECT '03','P1',1

CREATE TABLE #table2 (wh CHAR(2), item CHAR(2),date DATETIME, [move] INT)

INSERT INTO #table2 (wh, item, date, [move])

SELECT '01','P1','01/01/2010',100 UNION ALL

SELECT '02','P1','02/01/2010',200 UNION ALL

SELECT '02','P1','03/01/2010',50 UNION ALL

SELECT '02','P1','04/01/2010',100 UNION ALL

SELECT '03','P1','05/01/2010',20

-- Make a simple query

SELECT

s.item,

column1 = s.TotalStock,

column2 = m.TotalMove

FROM (

SELECT item, TotalStock = SUM(Stock)

FROM #Table1

GROUP BY item) s

INNER JOIN (

SELECT item, TotalMove = SUM([move])

FROM #table2

GROUP BY item) m ON m.item = s.item

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Kelvin Phayre (11/25/2010)
One last thing.
From your data.
INSERT INTO @Table2 VALUES (01,'P1','01/01/2010',100),
(02,'P1','02/01/2010',200),
(02,'P1','03/01/2010',50),
(02,'P1','04/01/2010',100),
(03,'P1','05/01/2010',20)
How do I get the following from Table2?
I want the last date in a column and the last movement quantity?

Do you mean the quantity and date of the most recent movement?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kelvin Phayre Ten Centuries Points: 1358 More actions · Answer 7

Yes I d.

So from the test data I would want Date=05/01/2010 and Move = 20

I did a crude subquery sql as follows.

...

(select top 1 date from table2 t2 where t2.wh in ('P1','P2','P3') and t1.item=t2.item orderby date desc) as[LAST DATE],

(select top 1 move from table2 t2 where t2.wh in ('P1','P2','P3') and t1.item=t2.item orderby date desc) as [LAST MOVE]

...