CODEWITHSUNDEEP
sqlsql-serversql-server-2008
kunal jangade
kunal jangade

Reputation: 7

balance stock in SQL SERVER

I am having 2 tables where i am supposed to show in qty, out qty and balance qty of items in stock i wrote my query as below: 

SELECT I.Date as Date, 
P.ResourceName as ItemName,
P.ResourceRate as Rate,
i.TotalInQty as InwardsQuantity,
s.TotalOutQty as OutwardsQuantity,
i.TotalInQty-S.TotalOutQty as Balance,
O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
 ON O.UnitCode = P.Unitcode
LEFT JOIN
(
  select sum(i.Qty) TotalInQty, Nameofitem
  from Inwards_Master I
  group by Nameofitem
) I
ON I.Nameofitem= P.ResourceName
LEFT JOIN
(
  select sum(s.qty) TotalOutQty, Nameofitem
  from Outwards_Master S 
  group by s.Nameofitem
) S
ON I.Date=S.Date 
AND I.Nameofitem =S.Nameofitem
WHERE P.TenderCode=1

the above query is giving me an error like below:

Msg 8120, Level 16, State 1, Line 14 Column 'Inwards_Master.Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

can any 1 please help me out

Upvotes: 0

Views: 1122

Answers (2)

Bridge
Bridge

Reputation: 30651

It's exactly as it says - so try including I.Date and S.Date in an aggregate:

SELECT I.Date as Date, 
P.ResourceName as ItemName,
P.ResourceRate as Rate,
i.TotalInQty as InwardsQuantity,
s.TotalOutQty as OutwardsQuantity,
i.TotalInQty-S.TotalOutQty as Balance,
O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
 ON O.UnitCode = P.Unitcode
LEFT JOIN
(
  select COALESCE(sum(i.Qty), 0) AS TotalInQty, Nameofitem, Min(I.Date) AS Date
  from Inwards_Master I
  group by Nameofitem
) I
ON I.Nameofitem= P.ResourceName
LEFT JOIN
(
  select COALESCE(sum(s.qty), 0) AS TotalOutQty, Nameofitem, Min(S.Date) AS Date
  from Outwards_Master S 
  group by s.Nameofitem
) S
ON I.Date=S.Date 
AND I.Nameofitem =S.Nameofitem
WHERE P.TenderCode=1

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Thats because you are not selecting the Date column from the table reference i not s, you should include it in the select list with an aggregate function MIN or MAX, or include it in the GROUP BY clause like so:

SELECT 
  I.Date as Date, 
  P.ResourceName as ItemName,
  P.ResourceRate as Rate,
  i.TotalInQty as InwardsQuantity,
  s.TotalOutQty as OutwardsQuantity,
  i.TotalInQty - S.TotalOutQty as Balance,
  O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P ON O.UnitCode = P.Unitcode
LEFT JOIN
(
  select 
    sum(i.Qty) TotalInQty, 
    "Date",
    Nameofitem
  from Inwards_Master I
  group by Nameofitem, "Date"
) I
ON I.Nameofitem = P.ResourceName
LEFT JOIN
(
  select 
    sum(s.qty) TotalOutQty, 
    "Date",
    Nameofitem
  from Outwards_Master S 
  group by s.Nameofitem, "Date"
) S  ON I.Date       = S.Date 
    AND I.Nameofitem = S.Nameofitem
WHERE P.TenderCode = 1;

Or: If you didn't need to GROUP BY date, then what date you want to get for each grouped Nameofitem?

You have to use an aggregate function in this case. For instance MAX(Date) to get the latest date like so:

...
LEFT JOIN
(
  select 
    sum(i.Qty) TotalInQty, 
    MAX("Date") LatestDate,
    Nameofitem
  from Inwards_Master I
  group by Nameofitem
) I

The same for s.

Upvotes: 1

Related Questions