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: 10279 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

Kelvin Phayre

Ten Centuries

Points: 1358

November 25, 2010 at 4:32 am

#1255519

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

Thanks.

Nakul Vachhrajani SSChampion Points: 10279 More actions · Answer 3

Nakul Vachhrajani

SSChampion

Points: 10279

November 25, 2010 at 4:34 am

#1255521

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]

...