Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
+---------------------+--------+
share|improve this question

2 Answers 2

up vote 2 down vote accepted
 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)
share|improve this answer
    
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
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 !!!

share|improve this answer
    
Thanks! +100 for you! –  giannis christofakis Dec 20 '11 at 19:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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