Item count

Question

HOw to get the item count by using ItemId, 

This is my code

SELECT     TOP (100) cr.CusRef, cr.CusRFQID, c.CusName, s.ShipName, cr.DelvTerm, cr.RFQDate, cr.RFQType, ItemMaster.ItemID
FROM         CusRFQ AS cr INNER JOIN
                      CusRFQ_Dtls ON cr.CusRFQID = CusRFQ_Dtls.CusRFQID INNER JOIN
                      ItemMaster ON CusRFQ_Dtls.ItemID = ItemMaster.ItemID LEFT OUTER JOIN
                      ShipDetail AS s ON cr.EndCustomer = s.ShipID LEFT OUTER JOIN
                      Customer AS c ON c.CusID = cr.CusID
WHERE     (cr.Deleted = '0')
ORDER BY cr.ModifiedDate DESC

How may i suppose to ge the Item count to ..

Please help me with this

Answer 1

I tried with this , but m getting an error

SELECT     TOP (100) cr.CusRef, cr.CusRFQID, c.CusName, s.ShipName, cr.DelvTerm, cr.RFQDate, cr.RFQType, Count (ItemMaster.ItemID) AS TOTNUMOFITEM
FROM         CusRFQ AS cr INNER JOIN CusRFQ_Dtls ON cr.CusRFQID = CusRFQ_Dtls.CusRFQID INNER JOIN
                      ItemMaster ON CusRFQ_Dtls.ItemID = ItemMaster.ItemID LEFT OUTER JOIN
                      ShipDetail AS s ON cr.EndCustomer = s.ShipID LEFT OUTER JOIN
                      Customer AS c ON c.CusID = cr.CusID
WHERE     (cr.Deleted = '0')
Group By ItemMaster.ItemID

Error:

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

Answer 2

Hi,

When you use aggregation and group by every column in the select list must be included in the group by, so for CustRFQ.CusRef must be in group by.

if you want to count all items for each customer you may use query like this

SELECT COUNT(ITEMID), CRFQOTID
FROM CustRFQ
GROUP BY CRFQOTID

I hope this is helpful.

Elmozamil Elamir

MyBlog

---

Please Mark it as Answered if it answered your question
OR mark it as Helpful if it help you to solve your problem
Elmozamil Elamir Hamid
http://elmozamil.blogspot.com

Answer 3

The correct Answer is this :

SELECT     CRD.CusRFQID, C.CusName, CR.CusRef, SD.ShipName, CR.DelvTerm, CR.RFQDate, CR.RFQType, COUNT(CRD.CusRFQID) AS NOI
FROM         CusRFQ_Dtls AS CRD INNER JOIN
                      CusRFQ AS CR ON CRD.CusRFQID = CR.CusRFQID LEFT OUTER JOIN
                      ShipDetail AS SD ON CR.EndCustomer = SD.ShipID LEFT OUTER JOIN
                      Customer AS C ON CR.CusID = C.CusID
GROUP BY CRD.CusRFQID, C.CusName, CR.CusRef, SD.ShipName, CR.DelvTerm, CR.RFQDate, CR.RFQType
ORDER BY CRD.CusRFQID

 you should call all the Column in the SELECT, to The GROUP BY 

Otherwise you will get an error as i posted above..

Thank you Guys