Getting totals from a third table

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

SQL Server 2008 Forums

Transact-SQL (2008)

Getting totals from a third table

Author

Topic

Spica66
Starting Member

23 Posts

Posted - 2012-08-08 : 14:21:44

In my SalesTable there is a SalesID. I need to use that to find all of the ItemId in the SalesLine Table. For each ItemID in the SalesLine table, there will be a cost in the ITM table where the ModuleType is equal to zero.

SalesTable (st)
-----------
SalesID
00653

SalesLine (sl)
----------
SALESID---ITEMID
00653-----9876
00653-----9877
00653-----9878

Inventablemodule (itm)
----------------
ITEMID---PRICE----ModuleType
9876-----51.50----0
9876-----51.50----1
9877-----35.75----0
9877-----35.75----1
9878-----12.50----0
9878-----12.50----1

I want to SUM all of those ITM costs where the ModuleType=0.

Here is my (incorrect) code so far:

SELECT st.CREATEDDATE
,st.SALESID
,st.CUSTACCOUNT
,st.DELIVERYSTATE
,st.SALESNAME
,st.NFWARRANTYNUMBER
,st.DIMENSION2_
,st.HRVYEARID
,st.HRVBRANDID
,st.HRVMODELID
,st.HRVCOLORID
,st.HRVRETAILSOLD
,st.HRVLIFECYCLESTATE
,st.HRVFPINVOICEAMOUNT
,( SELECT SUM(COSTPRICE)
FROM dbo.SALESLINE sl
WHERE sl.SALESID = st.SALESID) AS SLCOST--This gets standard cost

,( SELECT sl.SalesID, sl.ItemID, SUM(itm.Price) AS InvCost
FROM ((dbo.SalesTable st
LEFT JOIN SalesLine sl ON sl.SalesID = st.SalesID)
LEFT JOIN INVENTTABLEMODULE itm ON itm.ItemID= sl.ItemID AND itm.ModuleType=0)
GROUP BY st.SalesID) AS INVCOST --This gets inventory cost

FROM dbo.SalesTable st

I am getting the following error messages:

Msg 8120, Level 16, State 1, Line 19
Column 'SalesLine.SALESID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 116, Level 16, State 1, Line 23
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can anyone spot what I have done wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 14:57:50

[code]
SELECT st.SalesID,
SUM(itm.Price) AS InvCost
FROM SalesTable st
INNER JOIN SalesLine sl
ON sl.SalesID = st.SalesID
INNER JOIN InventTableModule itm
ON itm.ItemID = sl.ItemID
AND itm.ModuleType=0
GROUP BY SalesID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Spica66
Starting Member

23 Posts

Posted - 2012-08-08 : 15:02:01

This needs to be a subquery. I have made it one in this code:

SELECT st.CREATEDDATE
,st.SALESID
,st.CUSTACCOUNT
,st.DELIVERYSTATE
,st.SALESNAME
,st.NFWARRANTYNUMBER
,st.DIMENSION2_
,st.HRVYEARID
,st.HRVBRANDID
,st.HRVMODELID
,st.HRVCOLORID
,st.HRVRETAILSOLD
,st.HRVLIFECYCLESTATE
,st.HRVFPINVOICEAMOUNT
,( SELECT SUM(COSTPRICE)
FROM dbo.SALESLINE sl
WHERE sl.SALESID = st.SALESID) AS SLCOST--This gets standard cost

,(SELECT st.SalesID,
SUM(itm.Price) AS InvCost
FROM SalesTable st
INNER JOIN SalesLine sl ON sl.SalesID = st.SalesID
INNER JOIN InventTableModule itm ON itm.ItemID = sl.ItemID AND itm.ModuleType=0
GROUP BY st.SalesID) AS INVCOST

FROM dbo.SalesTable st

I get the following error:
Msg 116, Level 16, State 1, Line 24
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What the heck does THAT mean?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources