Select unique records with JOIN

[MikeGarvin]

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.

[Pradeep1210]

What happens if you add the keyword DISTINCT there?

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;

[MikeGarvin]

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

[Pradeep1210]

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?

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

[MikeGarvin]

Hi

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

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;

[MikeGarvin]

i

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