Join tables, count problems

Hi,

I have the following problem: I want to join 2 tables but the table that I want to join on has duplicates of the same record.

This is what my query looks like:
SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS Year
FROM executionreports AS e INNER JOIN
accounts AS a ON e.account = a.account
WHERE (e.ord_status = '0')
GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)
ORDER BY Expr1

and the output looks like this:

1AA1AA328420061CC1CC45320061CD1CD8420061MA1MA1167320061MA1MA828420067TR7TR2420067TS7TS3696320067TS7TS2676420067TW7TW34420067TW7TW18320067UW7UW3320067VE7VE4320067YP7YP405320067YP7YP23142006TESTTEST142006

The problem is that the count is too high. This is because the account table has several entries with 1MA and 7TS for example. How can I correct it so i basically joins on a distinct set of the account table records?

Thanks,

Tom

take a look at this maybe this will help you

create table #test(id int)
insert into #test values(1)
insert into #test values(1)
insert into #test values(1)
insert into #test values(2)
insert into #test values(3)
insert into #test values(4)

select id,count(id)
from #test
group by id

select id,count(distinct id)
from #test
group by id

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Isn't there an easy way to do it in one statement?
|||

what happens when you change COUNT(e.ord_status) to COUNT(DISTINCT e.ord_status)?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||no change because because I don't care about the e.ord_status here but about the a.account?
|||

Ok,

what about this then

SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS Year
FROM executionreports AS e INNER JOIN
(select account from accounts group by account) AS a ON e.account = a.account
WHERE (e.ord_status = '0')
GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)
ORDER BY Expr1

Can you post some sample data and DDL if this doesn't work?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||aaahh why didn't I try that ;) That did it, thx a lot