Problem to combine distinct with count and join

benanat · #1 (**permalink**) 03-26-06, 09:46

hi

i have two tables with shared ID, the shared id in TABLE A is primery key but in TABLE B it is not primery key, and in TABLE B there are more then one record with the shared key.

i need just the COUNT and SUM of tow of the fields in Table A but its have to be records that the key of them exist in Table B

with normal query i have no problem to use DISTINCT and solve this like this
**********************************************
SELECT
DISTINCT
TableA.ID
FROM
TableA,
TableB
WHERE
(TableA.ID = TableB.ID)
*********************************************
this return only one record even that there are two
*********************************************

but what i need is not the records, i need just the COUNT and SUM of tow of the fields in Table A but its have to be records that the key of them exist in Table B, and the problem is that the distinct doesn't effect the count and sum functions even that its only on fields from table A

the query of the count looks like this:
*********************************************

SELECT
DISTINCT
COUNT(TableA.ID) AS NumOfOrders,
SUM(TableA.TotalPrice) AS Total
FROM
TableA,
TableB
WHERE
(TableA.ID = TableB.ID)
***************************************
this one return counting and sum of the same record twice just because the id appear two times in table b, the distinct doesn't effect it
***************************************

does any one have an idea how to solve this with out changing the structure or the information of the tables???

thanks
jonathan

r937 · #2 (**permalink**) 03-26-06, 17:24

Code:

SELECT COUNT(TableA.ID) AS NumOfOrders
     , SUM(TableA.TotalPrice) AS Total
  FROM TableA
 where exists
       ( select 937
           from TableB
          WHERE ID = TableA.ID )