Please help!Query aggregate function for multiple

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help!Query aggregate function for multiple

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-09-20 : 23:16:04

Please help!Query aggregate function for multiple table
please help....thank you in advance

supp_det --table1
prodid nchar --foriegn key
suppid nchar
supqty int
spdate smalldatetime

promas --table2
prodid nchar --primary key
description varchar
modid nchar--foriegn key

prodet --table3
modid nchar --nchar --primary key
modesc varchar
uprice money

ordet --table3
orid nchar--foriegn key
prodid nchar
orqty int

Select promas.description,(sum(ordet.orqty) - sum(supp_det.supqty)) as [Re order] from ordet inner join(promas inner join supp_det on promas.prodid = supp_det.prodid) on promas.prodid=ordet.prodid
where [Re order] < 4
group by promas.description order by desc

the value of my [Re order] is the sum (sum(ordet.orqty) - sum(supp_det.supqty)).. i want to see the difference between the two field in difference table...but it not the output shown....

i want to see all my product below 4 so i can identify which product should i order....................plz help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 01:25:11

may be this

SELECT p.description,SUM(ordsum-supsum) AS [Re order]
FROM promas p
INNER JOIN (SELECT prodid,SUM(orqty) AS ordsum
            FROM ordet
            GROUP BY prodid) o
ON o.prodid=p.prodid
INNER JOIN (SELECT prodid,SUM(supqty) AS supsum
            FROM supp_det
            GROUP BY prodid) s
ON s.prodid=p.prodid
GROUP BY p.description
ORDER BY [Re order] desc