Quantity of entries

Question

I have the below tables.The first one has posts with a foreign key to a category table.I'm looking for a query that gives me the amount of posts on every category.

The post table.

 select pstOccuId,pstTitle from job_post;
+-----------+-------------------------------------------+
| pstOccuId | pstTitle                                  |
+-----------+-------------------------------------------+
|         1 | Software Engineer Recruit                 |
|         1 | Web Developer Recruit                     |
|         7 | Saxophonist                               |
|         5 | Construction Company looking for plumber. |
+-----------+-------------------------------------------+

The category table.

 select occuDscr,occuId from occupation_field;
+---------------------+--------+
| occuDscr            | occuId |
+---------------------+--------+
| Software Engineer   |      1 |
| Economics           |      2 |
| Structural Engineer |      3 |
| Legal Advisors      |      4 |
| Plumbers            |      5 |
| Social Advisors     |      6 |
| Musicians           |      7 |
+---------------------+--------+

I'm looking for a query that gives me something like this.

+---------------------+--------+
| occuDscr            | amount |
+---------------------+--------+
| Software Engineer   |      2 |
| Plumbers            |      1 |
| Musicians           |      1 |
+---------------------+--------+

Answer 1

 select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount` 
 from occupation_field a
 INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
 GROUP BY a.occuId;

If you want to display categories with 0 posts, you need to use LEFT JOIN instead of INNER. Also, for other RDMS except mysql you have to GROUP BY a.occuId, a.occuDscr (Mysql lets you have just GROUP BY a.occuId

UPDATE by @RolandoMySQLDBA

The output of @a1ex07's query looks like this:

mysql> select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount`
    ->  from occupation_field a
    ->  INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
    ->  GROUP BY a.occuId;
+--------+-------------------+--------+
| occuId | occuDscr          | amount |
+--------+-------------------+--------+
|      1 | Software Engineer |      2 |
|      5 | Plumbers          |      1 |
|      7 | Musicians         |      1 |
+--------+-------------------+--------+
3 rows in set (0.00 sec)

Answer 2

SELECT
    oc.occuDscr,pst.amount
FROM
    (SELECT COUNT(1) amount,pstOccuId 
    FROM job_post GROUP BY pstOccuId) pst
    INNER JOIN occupation_field oc
    ON (pst.pstOccuId = oc.occuId)
ORDER BY
    pst.amount DESC,oc.occuDscr
;

Here is your sample data:

mysql> use test
Database changed
mysql> drop table if exists occupation_field;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists job_post;
Query OK, 0 rows affected (0.07 sec)

mysql> create table occupation_field
    -> (occuDscr varchar(50),
    -> occuId int not null auto_increment,
    -> primary key (occuId));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into occupation_field (occuDscr)
    -> values ('Software Engineer'),('Economics'),
    -> ('Structural Engineer'),('Legal Advisors'),
    -> ('Plumbers'),('Social Advisors'),('Musicians');
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> create table job_post
    -> (pstOccuId int not null,pstTitle varchar(50));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into job_post values
    -> (1,'Software Engineer Recruit'),
    -> (1,'Web Developer Recruit'),
    -> (7,'Saxophonist'),
    -> (5,'Construction Company looking for plumber');
Query OK, 4 rows affected (0.21 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>    

Here is the result:

mysql> SELECT
    ->     oc.occuDscr,pst.amount
    -> FROM
    ->     (SELECT COUNT(1) amount,pstOccuId
    ->     FROM job_post GROUP BY pstOccuId) pst
    ->     INNER JOIN occupation_field oc
    ->     ON (pst.pstOccuId = oc.occuId)
    -> ORDER BY
    ->     pst.amount DESC,oc.occuDscr
    -> ;
+-------------------+--------+
| occuDscr          | amount |
+-------------------+--------+
| Software Engineer |      2 |
| Musicians         |      1 |
| Plumbers          |      1 |
+-------------------+--------+
3 rows in set (0.00 sec)

mysql>

To get counts for all occupations, query this way:

SELECT 
    oc.occuDscr,IFNULL(pst.amount,0) amount
FROM
    occupation_field oc LEFT JOIN
    (SELECT COUNT(1) amount,pstOccuId  
    FROM job_post GROUP BY pstOccuId) pst 
    ON (oc.occuId = pst.pstOccuId) 
ORDER BY 
    pst.amount DESC,oc.occuDscr 
; 

Here is that result:

mysql> SELECT
    ->     oc.occuDscr,IFNULL(pst.amount,0) amount
    -> FROM
    ->     occupation_field oc LEFT JOIN
    ->     (SELECT COUNT(1) amount,pstOccuId
    ->     FROM job_post GROUP BY pstOccuId) pst
    ->     ON (oc.occuId = pst.pstOccuId)
    -> ORDER BY
    ->     pst.amount DESC,oc.occuDscr
    -> ;
+---------------------+--------+
| occuDscr            | amount |
+---------------------+--------+
| Software Engineer   |      2 |
| Musicians           |      1 |
| Plumbers            |      1 |
| Economics           |      0 |
| Legal Advisors      |      0 |
| Social Advisors     |      0 |
| Structural Engineer |      0 |
+---------------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Answer 3

Just an alternative, you can also execute the below query, brings the same result.

SELECT occuDscr, COUNT(occuId) AS 'amount'
 FROM occupation_field of,job_post jp
 WHERE of.occuId = jp.pstOccuId
   GROUP BY occuDscr, occuId
   ORDER BY amount DESC;

SQL Fiddle