>> >I have something to do and maybe there is somebody out there having a
>> better solution.
>> >
>> >I have a table like that :
>> >
>> >JOB# TICKET# CLIENT# DONE
>> >1 1 1 yes
>> >1 2 1 no
>> >1 3 1 yes
>> >2 1 2 yes
>> >2 2 2 yes
>> >3 1 3 yes
>> >3 2 3 no
>> >
>> >I need a SQL select (or some other solution) to produce the following
>> >cursor :
>> >
>> >JOB# PROGRESS(%)
>> >1 66%
>> >2 100%
>> >3 50%
>> >
>> >So, the SQL result must have 1 record for each JOB# value and the second
>> column is defined as :
>> >
>> >100 * (How_Many_records_with_DONE=YES / How_Many_records
>> >
>> >If you have any decent solution for that please let me know. This
>> >cursor have to be updated every 10 seconds and there are milions
>> >of records in the main table.
>>
>> Actually, it'd be a little easier if you changed your done field either to
>> a logical or a numeric using 0/1, but in this case:
>>
>> SELECT job, (sum(IIF(done='yes',1,0))/count(job))*100 AS progress FROM
>> mytable GROUP BY job
>
>Even if you did this I don't expect that you'd get the results you
>want. Perhaps the numeric results would be accurate, but you'd need
>quite a machine to pull off that performance.
>
>This information is fed to the tables via some sort of manual or
>automated routine. I would have it update a second set of tables that
>are a summary of this information. Perhaps you should have a routine to
>constantly check that the totals are correct, perhaps check every few
>minutes or every hour. The summary table only requires three fields:
>
>Job#, # of Tickets, # of 'Yes's
>
>/Paul

Oops! One correction...I don't think it will work properly unless there is an ORDER BY job in the SELECT. Performace will be greatly improved if there is an index on job.

I agree with Paul and disagree at the same time. There are better ways to get a perfomance answer and one would be based on my original statement of changing the type of the done field to numeric. (IIF is not optimizable) This would be required if performance is desirable. Furthermore, I disagree that a second table is a good idea. I always prefer modifying data types over adding data to improve performance.