Mysql Count to return zero if no match

Question

I have a set of approx 9000 tutor ids in an array and i have put them in a string like:

(1,2, 3, 4,5,6,7,....9000,9001,9002)

so that i can use them in the following query:

select count(student_assignment.assignment_id) as total_assignment from 
student_assignment, assigned_tutor_fk where  assignment_status = 'closed'
 and assigned_tutor_fk in (1,2, 3, 4,5,6,7,..100,101,103...9000,9001,9002)
 group by assigned tutor_fk.

I want to calculate total number of rows associated with each tutor(assigned_tutor_fk), and those tutors which do not have an assignment ie those which do not have assignment record in the table i want to show their assignment count as 0, and i just want my query to return count and assigned_tutor_fk my table structure is:

    assignment_id | assigned_tutor_fk | assignment_date | student_id |
    |    1        |   2               |  22-01-2011     |  4         |
    |    2        |   3               |  14-03-2011     |  5         |

Im trying to get my output to be like this:

    |total_assignment | assigned_tutor_fk |
    |      5          | 4                 |
    |      2          | 7                 |
    |      0          | 8                 |

Update: I tthink i have not been able to express myself properly,i already have a list of tutors filtered on another criteria, it was very complex to combine these two queries so now i have a set of the tutor id's and i want the sum to be displayed as zero in case the tutors does not have assignment record. please help me on this as i don know wht to do now

Quassnoi · Accepted Answer · 2013-01-15 12:42:59Z

2

SELECT  t.id, COUNT(sa.assignment_id)
FROM    tutor t
LEFT JOIN
        student_assignement sa
ON      sa.assignment_tutor_fk = t.id
WHERE   t.id IN (1, 2, ..., 9002)
GROUP BY
        t.id

edited Jan 15, 2013 at 12:42

answered Jan 15, 2013 at 12:22

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

irohit786 Over a year ago

but i already have a list of tutors filtered on another criteria now i have a set of the tutor id's and further more i want the sum o be displayed as zero i case the tutors does not have assignment record

irohit786 Over a year ago

thank u this solved my problem, i admit at first i did not tink itr was suitable but then i realised it was the best possible solution in this situation..thanks u made may day!! cheers!!

dognose · Accepted Answer · 2013-01-15 12:26:59Z

0

dont put the tutors in a string. Select them from a table and do a LEFT JOIN with the assignment and FK table. Without knowing all of your tables, i'm guessing it would look like this:

select 
   t.tutorId,
   count(sa.assignment_id) as total_assignment
from 
   tutor t
LEFT JOIN
   assigned_tutor_fk fk
ON
   fk.assigned_tutor_fk = tutor.tutorId 
LEFT JOIN
   student_assignment sa
ON
  fk.assignment_id = sa.id
where  
   sa.assignment_status = 'closed' OR
   ISNULL(sa.assignment_status) -- if join fails.
group by 
   t.tutorId

Left Join retrieves all your values from the tutor table and merges it with the joined table IF there is a match. If not, NULL is inserted.

answered Jan 15, 2013 at 12:26

dognose

21k9 gold badges67 silver badges108 bronze badges

Comments

Mahmoud Gamal · Accepted Answer · 2013-01-15 13:25:00Z

0

SELECT 
  count(*) as total_assignment,
  assigned_tutor_fk  
FROM assignmentTable 
GROUP BY assigned_tutor_fk

edited Jan 15, 2013 at 13:25

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

answered Jan 15, 2013 at 12:18

Arun Killu

14.3k5 gold badges38 silver badges66 bronze badges

3 Comments

Alex Over a year ago

Might do it like this to get the result set he wants: SELECT count(*) AS total_assignment, assigned_tutor_fk FROM assignmentTable GROUP BY assigned_tutor_fk

irohit786 Over a year ago

i tried it before but it only gives the result of those tutors which have a record in the table, it does not return zero where no match is found..

Michael Fredrickson Over a year ago

@MahmoudGamal IFNULL won't work... in this case there isn't a row returned with a NULL count... instead, there's just no row returned at all...

Collectives™ on Stack Overflow

Mysql Count to return zero if no match

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related