1

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 |
+---------------------+--------+
3
 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)
  • I can't make your query work.Can you explain what the use of the letters 'a' and 'b'? – giannis christofakis Dec 20 '11 at 18:32
  • Table aliases - you can use optional AS : FROM occupation_field AS a , INNER JOIN ob_post AS b. Also I fixed some misprints in my answers (wrong table names), it should work now – a1ex07 Dec 20 '11 at 18:51
  • You still have the table spelled incorrectly. Change your INNER JOIN ob_post to INNER JOIN job_post. – RolandoMySQLDBA Dec 20 '11 at 19:00
  • Small correction INNER JOIN job_post.Now it works.I guess this select occuDscr,count(pstOccuId) as 'amount' from occupation_field inner join job_post on (pstOccuId=occuId) group by occuId; is the same. – giannis christofakis Dec 20 '11 at 19:01
  • Sorry for misprints :( – a1ex07 Dec 20 '11 at 19:04
3
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 !!!

0

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

  • 1
    This is the old (before 1992) SQL syntax. Most people use JOIN these days. Your query is practically (and functionally) the same as a1ex07's. – ypercubeᵀᴹ Sep 28 '15 at 8:47
  • Thanks @ypercube (always there)! Does this mean it might not be compatible with newer versions? Or other engines? – giannis christofakis Sep 28 '15 at 10:03
  • 1
    No, it's still in the SQL standard and allowed in all SQL products. But the JOIN is more flexible (you can have LEFT, RIGHT and FULL joins, besides the INNER and CROSS joins that the old syntax allows). And it's more easy to catch errors (like a missing condition) in more complex queries. – ypercubeᵀᴹ Sep 28 '15 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.