Ok, now I'm getting results, but it's not what I want. This works in my VFP version.

Here's what I'm getting back:

SELECT r.id,
       r.transaction_date,
       0 as batchid,
       r.telemrkt_company,
       t.company_code,
       count(*) as Cnt
FROM result_detail r
JOIN telemrkt_company t on r.telemrkt_company = t.id
GROUP BY r.id, r.transaction_date, r.telemrkt_company, t.company_cod


1	2006-01-03 00:00:00.000	0	135	WEST	1
2	2006-01-03 00:00:00.000	0	135	WEST	1
3	2006-01-03 00:00:00.000	0	135	WEST	1
4	2006-01-03 00:00:00.000	0	135	WEST	1
5	2006-01-03 00:00:00.000	0	135	WEST	1

Problem is, I need back one record with a count of 5. Because the Id is required, the
records are not grouped into one.

k





>First, T-SQL doesn't allow to use column aliases in GROUP BY. Second, you don't need to include "constant" columns like 'BatchId' into GROUP BY but you have to include all other non-aggregate columns.
>
>>Ok, I added GROUP BY BatchId and it still errors with 'Invalid column name 'BatchId'.'
>>
>>
>>
>>
>>>The GROUP BY is missing.
>>>
>>>>What's wrong with this? When I remove the count() function, it works fine.
>>>>
>>>>

>>>>SELECT r.id,
>>>>       r.transaction_date,
>>>>       0 as batchid,
>>>>       r.telemrkt_company,
>>>>       t.company_code,
>>>>       count(*) as Cnt
>>>>FROM result_detail r
>>>>JOIN telemrkt_company t on r.telemrkt_company = t.id
>>>>