Maybe I'm going about this wrong. Let me refresh the situation.

The records are loaded into result_detail in batches. The batch identifier is an Int column
called 'edi_file_log' which is a foreign key into a table also called 'edi_file_log'.

Here is edi_file_log, which is simply a header to result_detail:

id      file_name       total_read
-----------------------------------
1	MCM0103.txt	5
2	CONL0103.TXT	382

The detail records are loaded into results_detail. A user can select all or part of a batch to
work with, so I have to be able to pull the creation date, log file id, company name,
total number of records in the batch and the name of the user who has the records.

Since a user can load all or some of the batch, I would therefore need to see results like:

creation_date             edi_file_log  company_code  num_recs  used_by
-------------------------------------------------------------------------
2006-01-03 09:15:18.000   1             WEST           5         SMITH
2006-02-15 17:29:00.000   2             WEST           200       JONES
2006-02-15 17:29:00.000   2             WEST           187               

Notice that the second & third records are both from the same batch, but the second record shows
that user JONES has 200 records from the batch in use. The remainder of the batch's records are
unused as shown in record 3.

When a user selects records to use, a record is added to result_detail_info. This is a one-to-one
relationship to result_detail on result_detail.Id = result_detail_info.result_detail_id.

Once the record is created, the user's name is stored to it in the used_by field.

Thats all I need to do.

Here's the latest working code:

SELECT rd.transaction_date,
       rd.edi_file_log,
       tm.company_code as entityid,
       count(*) as totrecs,
       (CASE WHEN LEN(rdi.used_by)= 0 THEN NULL ELSE rdi.used_by END) AS used_by
FROM result_detail rd
JOIN telemrkt_company tm on rd.telemrkt_company = tm.id
LEFT OUTER JOIN result_detail_info rdi on rd.id = rdi.result_detail_id
WHERE rdi.status IS NULL
GROUP BY rd.transaction_date, rd.edi_file_log, tm.company_code, rdi.used_by
ORDER BY company_code

>I'm out of ideas for a moment.
>
>>Throws the 2 errors:
>>
>>Column 'rdi.status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>>
>>and
>>
>>Column 'rdi.used_by' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>>
>>
>>
>>
>>
>>>One more try

>>>GROUP BY rd.transaction_date, tm.company_code, ISNULL(rdi.status, ''), ISNULL(rdi.used_by, '')
>>>

>>>
>>>>Still only the one record.
>>>>
>>>>
>>>>
>>>>>Try

>>>>>SELECT rd.transaction_date,
>>>>>       0 as batchid,
>>>>>       tm.company_code as entityid,
>>>>>       count(*) as totrecs,
>>>>>       rdi.status,
>>>>>       rdi.used_by
>>>>>FROM result_detail rd
>>>>>JOIN telemrkt_company tm on rd.telemrkt_company = tm.id
>>>>>LEFT OUTER JOIN result_detail_info rdi on rd.id = rdi.result_detail_id AND rdi.status IS NULL
>>>>>GROUP BY rd.transaction_date, tm.company_code, rdi.status, rdi.used_by
>>>>>ORDER BY company_code
>>>>>

>>>>>