How do I total up my columns per row?

[susanh]

I am so stuck and trying to learn how to do this, but have just gotten confused.

All I want to do is add my columns for each row and display the total.

Here is my query. Can you guys help?

DROP TABLE temp_vbelts_table2

SELECT
IM_14ItemReceiptHistory.itemnumber,
IM_14ItemReceiptHistory.QtyReceivedPeriod1 AS 'QtyJan07',
IM_14ItemReceiptHistory.QtyReceivedPeriod2 AS 'QtyFeb07',
IM_14ItemReceiptHistory.QtyReceivedPeriod3 AS 'QtyMar07',
IM_14ItemReceiptHistory.QtyReceivedPeriod4 AS 'QtyApr07',
IM_14ItemReceiptHistory.QtyReceivedPeriod5 AS 'QtyMay07',
IM_14ItemReceiptHistory.QtyReceivedPeriod6 AS 'QtyJune07',
IM_14ItemReceiptHistory.QtyReceivedPeriod7 AS 'QtyJuly07',
IM_14ItemReceiptHistory.QtyReceivedPeriod8 AS 'QtyAug07',
IM_14ItemReceiptHistory.QtyReceivedPeriod9 AS 'QtySept07',
IM_14ItemReceiptHistory.QtyReceivedPeriod10 AS 'QtyOct07',
IM_14ItemReceiptHistory.QtyReceivedPeriod11 AS 'QtyNov07',
IM_14ItemReceiptHistory.QtyReceivedPeriod12 AS 'QtyDec07',
IM_14ItemReceiptHistory.QtyReceivedPeriod1+IM_14ItemReceiptHistory.QtyReceivedPeriod2+IM_14ItemReceipt
History.QtyReceivedPeriod3+IM_14ItemReceiptHistory.QtyReceivedPeriod4+IM_14ItemReceiptHistory.QtyReceiv
edPeriod5+IM_14ItemReceiptHistory.QtyReceivedPeriod6+IM_14ItemReceiptHistory.QtyReceivedPeriod7+IM_14It
emReceiptHistory.QtyReceivedPeriod8+IM_14ItemReceiptHistory.QtyReceivedPeriod9+IM_14ItemReceiptHistory
.QtyReceivedPeriod10+IM_14ItemReceiptHistory.QtyReceivedPeriod11+IM_14ItemReceiptHistory.QtyReceivedPer
iod12+IM_14ItemReceiptHistory.QtyReceivedPeriod13 AS 'TotalYTD'
INTO temp_vbelts_table2
FROM temp_vbelts_table1, IM_14ItemReceiptHistory
WHERE temp_vbelts_table1.itemnumber = IM_14ItemReceiptHistory.ItemNumber
AND IM_14ItemReceiptHistory.Year IN ('2007')
GROUP BY temp_vbelts_table1.itemnumber, IM_14ItemReceiptHistory.itemnumber, IM_14ItemReceiptHistory.QtyReceivedPeriod1, IM_14ItemReceiptHistory.QtyReceivedPeriod2, IM_14ItemReceiptHistory.QtyReceivedPeriod3,
IM_14ItemReceiptHistory.QtyReceivedPeriod5, IM_14ItemReceiptHistory.QtyReceivedPeriod6, IM_14ItemReceiptHistory.QtyReceivedPeriod7,
IM_14ItemReceiptHistory.QtyReceivedPeriod8, IM_14ItemReceiptHistory.QtyReceivedPeriod9, IM_14ItemReceiptHistory.QtyReceivedPeriod10,
IM_14ItemReceiptHistory.QtyReceivedPeriod11, IM_14ItemReceiptHistory.QtyReceivedPeriod12, IM_14ItemReceiptHistory.QtyReceivedPeriod13
ORDER BY IM_14ItemReceiptHistory.itemnumber

Thanks Sue

 

[SQLDenis]

select IM_14ItemReceiptHistory.QtyReceivedPeriod1 +
IM_14ItemReceiptHistory.QtyReceivedPeriod2 +
IM_14ItemReceiptHistory.QtyReceivedPeriod3 +
IM_14ItemReceiptHistory.QtyReceivedPeriod4 +
IM_14ItemReceiptHistory.QtyReceivedPeriod5 +
IM_14ItemReceiptHistory.QtyReceivedPeriod6 +
IM_14ItemReceiptHistory.QtyReceivedPeriod7 +
IM_14ItemReceiptHistory.QtyReceivedPeriod8 +
IM_14ItemReceiptHistory.QtyReceivedPeriod9 +
IM_14ItemReceiptHistory.QtyReceivedPeriod10 +
IM_14ItemReceiptHistory.QtyReceivedPeriod11 +
IM_14ItemReceiptHistory.QtyReceivedPeriod12 AS 'Qty2007'
rest of query

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

Sue, can you provide some simple data and desired result? I think you need some SUM() here, but not sure.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

OK, how about:

SELECT IM_14Hist.itemnumber,
       SUM(IM_14Hist.QtyReceivedPeriod1) AS 'QtyJan07',
.....
       SUM(IM_14Hist.QtyReceivedPeriod12) AS 'QtyDec07', 
       SUM(IM_14Hist.QtyReceivedPeriod1+
           IM_14Hist.QtyReceivedPeriod2+
...
           IM_14Hist.QtyReceivedPeriod12) AS TotalYTD
FROM IM_14ItemReceiptHistory IM_14Hist
INNER JOIN temp_vbelts_table1
      ON temp_vbelts_table1.itemnumber =
         IM_14Hist.ItemNumber
WHERE  IM_14Hist.Year = 2007
GROUP BY IM_14Hist.itemnumber

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[susanh]

Sure,

My Columns have data like this

QtyReceivedPeriod1 5
QtyReceivedPeriod2 1
QtyReceivedPeriod3 0


I would like my output of date for the query to display like the following:

QtyReceivedPeriod1 QtyReceivedPeriod2 QtyReceivedPeriod3 Tot
5 1 0 6

 

[susanh]

Well I tried that but keep getting the lovely

Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near 'History'.

 

[susanh]

Ok. I apologize for bugging you guys with this, but I tried it again and it worked!