Creating stats

[transparent]

Hello!

I have two tables in a database.

jobtypes -> a single column table which is a list of job types.

ie

'''''''''
'Jobtype'
'''''''''
' type1 '
' type2 '
' type3 '
'''''''''
CV -> a two column table (cv_id and jobtype)

ie
'''''''''''''''''''
' cv_id ' jobtype '
'''''''''''''''''''
' 1 ' type2 '
' 2 ' type2 '
' 3 ' type1 '
' 4 ' type2 '
'''''''''''''''''''

I want to create a statistics table showing the number of cv items per job type.

So I wrote the following SQL:

strSQL = "select jobtypes.jobtype, count(CV.jobtype) as count from jobtypes,cv where jobtypes.jobtype=cv.jobtype group by jobtypes.jobtype"


this returns

'''''''''''''''''''
' Jobtype ' count '
'''''''''''''''''''
' type2 ' 3 '
' type1 ' 1 '
'''''''''''''''''''

however I want:

'''''''''''''''''''
' Jobtype ' count '
'''''''''''''''''''
' type2 ' 3 '
' type1 ' 1 '
' type3 ' 0 '
'''''''''''''''''''

How do I do this?

PS I'm using a MYSQL database.

Cheers

 

[JamesThornley]

You may be able to do this with an outer join, but as the cv.jobtype will be returned as null I'm not sure how mysql will deal with counting it. It may be possible to use a ifnull function, but I'm not familiar enough with mysql to help you with this

Alternatively it can be done with a union

select jobtypes.jobtype, count(CV.jobtype) as count from jobtypes,cv where jobtypes.jobtype=cv.jobtype group by jobtypes.jobtype
union
select jobtypes.jobtype, 0 as count
from jobtypes
where not exists (select * from cv where jobtypes.jobtype=cv.jobtype)