query question?

[pser]

I have several tables which i want to get information from and i have a query with lots of inner join which works fine. But one of the table contain a field named numberOfItem which i want to sum and list with the others.
Queries are like this:

Query1:
SELECT Freight.FREIGHTID, Sum(FreightType.NUMBEROF) AS SumNUM
FROM Freight INNER JOIN FreightType ON Freight.FREIGHTID = FreightType.FREIGHTID
GROUP BY Freight.FREIGHTID;

Result1:
FreightId------SumOfNum
1---------------50
2---------------105

Query2:

SELECT DISTINCT Freight.FREIGHTID, Freight.ProjectNo, Consignor.Consignor, Consignee.Consignee, FreightType.NUMBEROF
FROM (Consignor INNER JOIN (Consignee INNER JOIN Freight ON Consignee.CONSIGNEEID = Freight.ConsigneeID) ON Consignor.CONSIGNORID = Freight.ConsignorID) INNER JOIN FreightType ON Freight.FREIGHTID = FreightType.FREIGHTID;

Result2:
FreightId--ProjectNo--Category--Consignor--Consignee--NumberOf
1----------111--------CatA------ConNA------ConEA-----20
1----------111--------CatA------ConNA------ConEA-----30
2----------222--------CatB------ConNB------ConEB-----5
2----------222--------CatB------ConNB------ConEB-----100


But how to combine that with the two queries so i get this result:

Result3:
FreightId--ProjectNo--Category--Consignor--Consignee--NumberOf
1----------111--------CatA------ConNA------ConEA-----50
2----------222--------CatB------ConNB------ConEB-----105

I appreciate tip to solve this issue

cheers
Paal

 

[RV]

SELECT Freight.FREIGHTID, Freight.ProjectNo, Consignor.Consignor, Consignee.Consignee, SUM(FreightType.NUMBEROF)
FROM (Consignor INNER JOIN (Consignee INNER JOIN Freight ON Consignee.CONSIGNEEID = Freight.ConsigneeID) ON Consignor.CONSIGNORID = Freight.ConsignorID) INNER JOIN FreightType ON Freight.FREIGHTID = FreightType.FREIGHTID
GROUP BY Freight.FREIGHTID, Freight.ProjectNo, Consignor.Consignor, Consignee.Consignee;

RV

 

[pser]

So it was just matter of grouping by all selected fields you wanted.

Thanks

Paal