MySQL CONCAT COUNT query
Posted: Wed Apr 11, 2012 10:26 am
Hi all
I have a database of alumni (or alumnuses, or whatever they're called) and what they are doing now, something like...
[syntax]
NAME | UDept | UDept2 | PGDept | TypeOfJob
--------------------------------------------------------------------
John | Music | none | none | Creative Arts
--------------------------------------------------------------------
Paul | French | Music | none | Creative Arts
--------------------------------------------------------------------
George | French | German | Music | Accountancy
--------------------------------------------------------------------
Ringo | History | French | none | Accountancy
--------------------------------------------------------------------
Eric | Politics | none | Music | Administration
--------------------------------------------------------------------
Marc | none | none | Music | Creative Arts
[/syntax]
... where UDept and UDept2 are undergraduate departments (ie it's possible to do dual honours) and PGDept is the Postgraduate department the student was part of.
Now, I'm trying to get a count for each 'TypeOfJob' (ie a user selects the type from a form), for example:
If I select job type 'Creative Arts' it should give a totals of French 1, Music 3
If I select job type 'Accountancy' it should give a totals of French 2, German 1, History 1, Music 1
(these totals are then used to produce a Google chart)
However I'm not sure of the correct syntax to concatenate the different departments together...
The current query I use is:
which obviously only counts just the UDept results. I've tried various ways of using CONCAT but don't seem to be getting anywhere, so I could do with some pointers, please!
I have a database of alumni (or alumnuses, or whatever they're called) and what they are doing now, something like...
[syntax]
NAME | UDept | UDept2 | PGDept | TypeOfJob
--------------------------------------------------------------------
John | Music | none | none | Creative Arts
--------------------------------------------------------------------
Paul | French | Music | none | Creative Arts
--------------------------------------------------------------------
George | French | German | Music | Accountancy
--------------------------------------------------------------------
Ringo | History | French | none | Accountancy
--------------------------------------------------------------------
Eric | Politics | none | Music | Administration
--------------------------------------------------------------------
Marc | none | none | Music | Creative Arts
[/syntax]
... where UDept and UDept2 are undergraduate departments (ie it's possible to do dual honours) and PGDept is the Postgraduate department the student was part of.
Now, I'm trying to get a count for each 'TypeOfJob' (ie a user selects the type from a form), for example:
If I select job type 'Creative Arts' it should give a totals of French 1, Music 3
If I select job type 'Accountancy' it should give a totals of French 2, German 1, History 1, Music 1
(these totals are then used to produce a Google chart)
However I'm not sure of the correct syntax to concatenate the different departments together...
The current query I use is:
Code: Select all
SELECT COUNT(TypeOfJob),UDept FROM alumniDetails WHERE FormStatus='Approved' AND CurrentStatus='Employed' AND TypeOfJob='~user selected~' GROUP BY CONCAT(UDept,UDept2,PGDept)