I'm quering about 5 tables using inner join. On one of the tables i just
want to count or sum up the results. Here is the example I'm trying to get
working. When I add the SUM(Private.PrivateIDs) I get an error. Is
something like this possible?

SELECT Units.UnitServiceLine, Units.UnitName,
Staffing_Types.StaffingType, Unit_Request.UnitOnDutyStaff,
SUM(Private.PrivateIDs)
FROM Unit_Info
INNER JOIN Unit_Request ON Unit_Info.UIID = Unit_Request.UIID INNER JOIN
Units ON Unit_Info.UnitID = Units.UID
INNER JOIN Staffing_Types ON Unit_Request.STID=Staffing_Types.STID
INNER JOIN Private ON Unit_Info.UIID = Private.UIID

On Wed, 18 May 2005 09:51:13 -0700, LU wrote:

Hi LU,

Without knowing your table structure, sample data and expected output,
the best I can offer is a wild guess:

SELECT Units.UnitServiceLine, Units.UnitName,
Staffing_Types.StaffingType, Unit_Request.UnitOnDutyStaff,
PS.SumOfPrivateIDs
FROM Unit_Info
INNER JOIN Unit_Request
ON Unit_Info.UIID = Unit_Request.UIID
INNER JOIN Units
ON Unit_Info.UnitID = Units.UID
INNER JOIN Staffing_Types
ON Unit_Request.STID=Staffing_Types.STID
INNER JOIN (SELECT UIID, SUM(PrivateIDs) AS SumOfProvateiDs
FROM Private
GROUP BY UIID) AS PS
ON Unit_Info.UIID = PS.UIID
(untested)

If this doesn't help, then check www.aspfaq.com/5006, then post again,
following the instructions therein.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)