Getting 2 SUMs from the same table

[Astra]

Hi All

I'm really stuck on this one so would appreciate any help you can give.

In essence, I have 1 SQL 2000 table with rows of data logging stock
movement. To differenciate between a stock sale and a stock receipt the
table has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals a
receipt.

I've been asked to report on this data by suming the total qty used on
invoices and the total qty recvd for each stock item, but I can't figure out
how I sum the same rows twice in the one query.

For example, my query is as follows:

select st.stockid as 'STYLE',
s.picture as 'COLOUR',
'' as 'IN FIRST IN LAST WEEK',
'' as 'THIS WEEK IN',
'' as 'TOTAL IN',
'' as 'OUT FIRST OUT LAST WEEK',
SUM(st.quantity ) as 'THIS WEEK OUT',
'' as 'TOTAL OUT',
'' as 'REMAINING',
'' as 'TOTAL DIGESTION %'
from stocktransactio ns st, stock s
where st.stockid = s.stockid and
st.transactiont ype in (8,7) and
st.transactiond ate >= '2005-07-12 00:00:00' and
st.transactiond ate <= '2005-07-12 23:59:59'
group by st.stockid,s.pi cture
order by st.stockid

Apart from the 'THIS WEEK OUT' column SUMing all of the stock sales by
transactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of the
transactions by transactiontype 3, so that I get the following results:

STYLE COLOUR .... THIS WEEK IN .... THIS WEEK OUT .......
IVP Red 12 23
STP Blue 4 15
etc etc

My problem is that I don't want to exclude a stock item if it hasn't got a
row/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking too
much of SQL?

My table schemas are as follows:

create table STOCKTRANSACTIO NS
(
STOCKTRANSACTIO NID T_STOCKTRANSACT IONSDOMAIN not null
identity(1,1),
TRANSACTIONTYPE smallint not null,
TRANSACTIONDATE datetime null ,
REFERENCE varchar(40) null ,
Comment varchar(255) null ,
STOCKID T_STOCKDOMAIN null ,
DESCRIPTION varchar(255) null ,
UNITOFSALE varchar(20) null ,
WAREHOUSEID T_WAREHOUSESDOM AIN null ,
PEOPLEID T_PEOPLEDOMAIN null ,
AccountID T_AccountsDomai n null ,
AgentID T_AgentsDomain null ,
PLRate float null ,
CONTACTID T_CONTACTDETAIL SDOMAIN null ,
JOBID T_JOBSDOMAIN null ,
QUANTITY float null ,
CURRENCYID T_CURRENCIESDOM AIN null ,
SELLINGPRICE float null ,
DISCOUNTPERCENT float null ,
COSTPRICE float null ,
MINIMUMPRICE float null ,
TILLID T_TILLSDOMAIN null ,
UserID T_UsersDomain null ,
ClockDate DateTime null ,
TimeStamp TimeStamp ,
constraint pk_stocktransac tions primary key (STOCKTRANSACTI ONID)
)
go

create table STOCK
(
STOCKID T_STOCKDOMAIN not null,
NAME varchar(40) not null,
PICTURE varchar(40) null ,
WEIGHT float null ,
VOLUME float null ,
BARCODE smallint null ,
NumberOfPriceBr eaks SmallInt not null default 1,
STOCKCATEGORYID T_STOCKCATEGORI ESDOMAIN null ,
SALESNOMINALID T_NOMINALACCOUN TSDOMAIN null ,
PURCHASENOMINAL ID T_NOMINALACCOUN TSDOMAIN null ,
SELLINGCOMMENT varchar(255) null ,
INCLUDESELLINGC OMMENT TinyInt null ,
DISPLAYSELLINGC OMMENT TinyInt null ,
COSTCOMMENT varchar(255) null ,
DISPLAYCOSTCOMM ENT TinyInt null ,
PRODUCTTRACKING smallint null ,
ITEMTYPE smallint null ,
VALUATIONPRICE float not null default
0.00 ,
INCLUDEINCUSTOM ERSTURNOVER TinyInt null ,
INCLUDEINAGENTS TURNOVER TinyInt null ,
SUPERCEDED TinyInt null ,
SUPERCEDEDBY T_STOCKDOMAIN null ,
SUPPLIERID T_PEOPLEDOMAIN null ,
SUPPLIERSTOCKID varchar(40) null ,
SUPPLIERCOMMENT varchar(255) null ,
NEXTSERIALNUMBE R int null ,
SERIALNUMBERLEN GTH smallint null ,
SERIALNUMBERPRE FIX varchar(10) null ,
SERIALNUMBERSUF FIX varchar(10) null ,
SERIALNUMBERPRE FIXLENGTH smallint null ,
SERIALNUMBERSUF FIXLENGTH smallint null ,
TIMESTAMP timestamp not null,
constraint pk_stock primary key (STOCKID)
)
go

Thanks

Robbie

[Madhivanan]

Re: Getting 2 SUMs from the same table

Dont repeat the question

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/6233b885a0869eb8/1e663f7fc429d1a3#1e663f7fc429d1a3

Madhivanan

[jennifer1970@hotmail.com]

Re: Getting 2 SUMs from the same table

Robbie,

You have some data types in your schema that aren't really data types.
You have STOCKTRANSACTIO NID as a data type of
T_STOCKTRANSACT IONSDOMAIN. Are you using SQL Server? How about
posting with good data types and some inserts so people can help you
better.

Thanks,
Jennifer

[Erland Sommarskog]

Re: Getting 2 SUMs from the same table

(jennifer1970@h otmail.com) writes:[color=blue]
> You have some data types in your schema that aren't really data types.
> You have STOCKTRANSACTIO NID as a data type of
> T_STOCKTRANSACT IONSDOMAIN. Are you using SQL Server? How about
> posting with good data types and some inserts so people can help you
> better.[/color]

I assume that these are so-called user-defined data types created with
sp_addtype.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp