How to remove duplicate rows before applying GROUP BY clause 1

[atisman89]

I want to apply a simple SELECT - GROUP BY statement such as:

SELECT AVG(Table1.Col_C)
FROM Table1 LEFT OUTER JOIN Table2 ON ...
GROUP BY Table1.Col_A

But the problem is that the result set after the left outer join has some duplicate rows. For example...

Table1.Col_A Table1.Col_B Table1.Col_C
-------------- -------------- --------------
a1 b1 c1
a1 b1 c1
a1 b2 c2
a1 b3 c1


As a result, the aggregate function AVG for group "a1" computes average over all duplicate values resulting in:

(c1 + c1 + c2 + c1) / 4.0

What I want to do is to remove redundancy based on Col_B within a group and computes like:

(c1 + c2 + c1) / 3.0

I cannot get this results by using AVG(DISTINCT Table1.Col_C) since it removes redundancy in Col_C NOT in Col_B resulting in:

(c1 + c2) / 2.0

Could anyone help me with solving this problem in SQL?
I thought this might be done if I could remove duplicate rows before applying GROUP BY clause but not sure how to do so. Any help will be appreciated.

 

[PHV]

A starting point:
SELECT AVG(Col_C)
FROM (SELECT DISTINCT Col_A, Col_B, Col_C
FROM Table1 LEFT OUTER JOIN Table2 ON ...)
GROUP BY Col_A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886