Select unique records with JOIN

Hi

I'm trying to select some rows but keep getting double the number of records because of duplicates for each record in table calltypes. Here's what I have:

SELECT DISTINCT TA.GroupName, SUM(TB.Customer_Call_Cost), COUNT(TB.ID)
FROM CallTypes AS TA INNER JOIN VoiceTable AS TB ON TA.GroupName=TB.Customer_Call_Group
WHERE TB.Customer_Index=1 And TB.Date_Time_Called>=#5/1/2009# And TB.Date_Time_Called<=#5/31/2009 23:59:59#
GROUP BY TA.GroupName
ORDER BY TA.GroupName;

It returns this:

GroupName Expr1001 Expr1002
International 76.89 140
Mobile 33.32 252
National 3.51 64
Other 0.15 8

When it should return this:

GroupName Expr1001 Expr1002
International 76.89 70
Mobile 33.32 126
National 3.51 32
Other 0.15 4

I'd be grateful for help. Thanks.

What happens if you add the keyword DISTINCT there?

Code:

---

SELECT DISTINCT TA.GroupName, SUM(TB.Customer_Call_Cost), COUNT(DISTINCT TB.ID)
FROM CallTypes AS TA INNER JOIN VoiceTable AS TB ON TA.GroupName=TB.Customer_Call_Group
WHERE TB.Customer_Index=1 And TB.Date_Time_Called>=#5/1/2009# And TB.Date_Time_Called<=#5/31/2009 23:59:59#
GROUP BY TA.GroupName
ORDER BY TA.GroupName;

---

Thanks. It might work in other DBs than Access but in Access I get:

Syntax error missing operator in query expression 'count(distinct tb.id)'

okk.. So you are using Ms-Access.

The DISTINCT keyword is actually not required when a GROUP BY is there. What happens if you remove it?

sql Code:

SELECT TA.GroupName, SUM(TB.Customer_Call_Cost), COUNT(TB.ID)
FROM CallTypes AS TA 
    INNER JOIN VoiceTable AS TB ON TA.GroupName=TB.Customer_Call_Group
WHERE TB.Customer_Index = 1 
    And TB.Date_Time_Called >= #5/1/2009# 
    And TB.Date_Time_Called <= #5/31/2009 23:59:59#
GROUP BY TA.GroupName
ORDER BY TA.GroupName

Hi

I think I've fixed it. Results are ok now:

Code:

---

SELECT DISTINCT TB.GroupName, SUM(TA.Customer_Call_Cost), COUNT(TA.ID)
FROM VoiceAutoCRCustomerInv AS TA INNER JOIN (select distinct groupname from calltypes group by groupname having count(*) > 1)  AS TB ON TB.GroupName=TA.Customer_Call_Group
WHERE TA.Customer_Index=1 And TA.Date_Time_Called>=#5/1/2009# And TA.Date_Time_Called<=#5/31/2009 23:59:59#
GROUP BY TB.GroupName
ORDER BY TB.GroupName;

---

i

Pradeep, just tried your sql but gives same incorrect results.