Page 1 of 1

MySQL CONCAT COUNT query

Posted: Wed Apr 11, 2012 10:26 am
by Eric Praline
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:

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)
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!

Re: MySQL CONCAT COUNT query

Posted: Thu Apr 19, 2012 12:20 pm
by Robert07
It looks to me like you'll either need to create temp tables and run multiple queries or export the data for one job type and loop through the results to come up with the counts in php (or whatever server side language you are familiar with)

Re: MySQL CONCAT COUNT query

Posted: Fri Apr 20, 2012 2:39 pm
by x_mutatis_mutandis_x
Your table is not normalized so its not that easy to perform aggregations over multiple columns in a straight forward way. So the best way to achive this is to do what Robert suggested.

But if you insist on using just SQL (spoilt like me), try this query and let me know what you get:

Code: Select all

select Dept,
count(NAME)

from (

select UDept as Dept,
NAME

from aluminiDetails

where TypeOfJob = '~user-selected~'

union 

select UDept2 as Dept,
NAME

from aluminiDetails

where TypeOfJob = '~user-selected~'

union

select PGDept as Dept,
NAME

from aluminiDetails

where TypeOfJob = '~user-selected~') as temp_aluminiDetails

group by Dept

Re: MySQL CONCAT COUNT query

Posted: Wed May 16, 2012 5:32 am
by Eric Praline
Sorry, I had to work on another project for a few weeks so didn't get chance to test your reply... but it seems to work! :D

I originally ended up using 3 queries and totalling up the results, but using the 'all-in-one' query looks like it gets the same totals.
I've tried it on a few different 'types of job' and the results I'm getting seem to match up with the multiple query option.

Cheers!