Getting a ?DISTINCT? result

[Tim]

Tables ProjectAmount & ProjectBid both have Project ID in common, and both
tables can have many of these.
I need the SUM(ProjectAmount.Amount) for any related Project_ID in
ProjectBid WHERE Sum(ProjectBid.Awarded_Type) = 0.
I could also say: for any related Project_ID in ProjectBid WHERE All of the
ProjectBid.Awarded = False.

My problem is that no matter how I spin it, for these project the criteria,
the SUM that I'm after is being multiplied by the number of matching records
in ProjectBid. I thought that this is where DISTINCT came in, but it's not
working for and I'm not understanding why or what I can do about it.

This is what I'm using right now. It's Union'ed with others so I need to
only select these fields.
Hoping someone can enlighten me Thanks

SELECT A.PROJECT_ID, 0 Awarded, SUM(AMOUNT) UN_Awarded
FROM PROJECTAMOUNTS A
INNER JOIN PROJECTBID B
ON A.PROJECT_ID = B.PROJECT_ID
GROUP BY A.PROJECT_ID
HAVING SUM(B.AWARDED_TYPE)=0

 

[MGFoster]

Tables ProjectAmount & ProjectBid both have Project ID in common, and both
tables can have many of these.
I need the SUM(ProjectAmount.Amount) for any related Project_ID in
ProjectBid WHERE Sum(ProjectBid.Awarded_Type) = 0.
I could also say: for any related Project_ID in ProjectBid WHERE All of the
ProjectBid.Awarded = False.

My problem is that no matter how I spin it, for these project the criteria,
the SUM that I'm after is being multiplied by the number of matching records
in ProjectBid. I thought that this is where DISTINCT came in, but it's not
working for and I'm not understanding why or what I can do about it.

This is what I'm using right now. It's Union'ed with others so I need to
only select these fields.
Hoping someone can enlighten me Thanks

SELECT A.PROJECT_ID, 0 Awarded, SUM(AMOUNT) UN_Awarded
FROM PROJECTAMOUNTS A
INNER JOIN PROJECTBID B
ON A.PROJECT_ID = B.PROJECT_ID
GROUP BY A.PROJECT_ID
HAVING SUM(B.AWARDED_TYPE)=0

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually (actually, always), a type is not summed. If awarded_type is a
Yes/No (Boolean) data type perhaps putting the criteria in a WHERE
clause instead of the HAVING clause would work.

SELECT A.PROJECT_ID, 0 Awarded, SUM(AMOUNT) UN_Awarded
FROM PROJECTAMOUNTS A INNER JOIN PROJECTBID B
ON A.PROJECT_ID = B.PROJECT_ID
WHERE B.AWARDED_TYPE = 0
GROUP BY A.PROJECT_ID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtAUSoechKqOuFEgEQIm8QCgy2ooCP+ZhQaHuregSmhKgmuLO6QAoPlY
4TeZhr7ERKY31IdIbnfIavcY
=TVje
-----END PGP SIGNATURE-----