query multi tables, count/sum 1 of the tables

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:

>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
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)