MySQL CONCAT COUNT query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

MySQL CONCAT COUNT query

Post 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!
User avatar
Robert07
Forum Contributor
Posts: 113
Joined: Tue Jun 17, 2008 1:41 pm

Re: MySQL CONCAT COUNT query

Post 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)
x_mutatis_mutandis_x
Forum Contributor
Posts: 160
Joined: Tue Apr 17, 2012 12:57 pm

Re: MySQL CONCAT COUNT query

Post 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
Eric Praline
Forum Commoner
Posts: 32
Joined: Wed Aug 29, 2007 8:37 am

Re: MySQL CONCAT COUNT query

Post 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!
Post Reply