Hi all,
Was wandering if someone could take me out of my misery. have been struggling with this for a few hours without success.
I have to calculate the percentage of tasks completed by category.
For a task to be considered as completed it has to meet the following criterias:
1. Task is marked as N/A
2. Task is marked as Applicable and has a value entered for job A and Job B
Fields:
1. Category
2. Task
3. JobA
4. JobB
This gives me the proper format. I.e. Each categeory and the percentage. However, I am ommiting the fact where JobA and JobB also have to be completed and this is where my problem starts.
select category, count(task)/Count(*) * 100 from tblTasks group by category
here I tried to get the number of tasks completed but it ignores the category. So I have the total number of tasks completed and the same number will appear per category.
select category, (select count(task) from tblTasks where (tbltasks.task = NA)
or ((tbltasks.jobA is not null) and (tbltasks.jobB is not null)), count(*) from tbltasks
group by category
'If I include a group by function within the subquery, i got an error saying that no more than 1 result set can be return - which makes sense
Please, anyone have any pointers as to how I can get the number of completed tasks (which is based on the expression described above) and the total number of tasks per category?
Many thanks
Baboudi
Was wandering if someone could take me out of my misery. have been struggling with this for a few hours without success.
I have to calculate the percentage of tasks completed by category.
For a task to be considered as completed it has to meet the following criterias:
1. Task is marked as N/A
2. Task is marked as Applicable and has a value entered for job A and Job B
Fields:
1. Category
2. Task
3. JobA
4. JobB
This gives me the proper format. I.e. Each categeory and the percentage. However, I am ommiting the fact where JobA and JobB also have to be completed and this is where my problem starts.
select category, count(task)/Count(*) * 100 from tblTasks group by category
here I tried to get the number of tasks completed but it ignores the category. So I have the total number of tasks completed and the same number will appear per category.
select category, (select count(task) from tblTasks where (tbltasks.task = NA)
or ((tbltasks.jobA is not null) and (tbltasks.jobB is not null)), count(*) from tbltasks
group by category
'If I include a group by function within the subquery, i got an error saying that no more than 1 result set can be return - which makes sense
Please, anyone have any pointers as to how I can get the number of completed tasks (which is based on the expression described above) and the total number of tasks per category?
Many thanks
Baboudi
