a77BEVAJ

1select pstOccuId,pstTitle from job_post;
2+-----------+-------------------------------------------+
3| pstOccuId | pstTitle                                  |
4+-----------+-------------------------------------------+
5|         1 | Software Engineer Recruit                 |
6|         1 | Web Developer Recruit                     |
7|         7 | Saxophonist                               |
8|         5 | Construction Company looking for plumber. |
9+-----------+-------------------------------------------+
10	
11select occuDscr,occuId from occupation_field;
12+---------------------+--------+
13| occuDscr            | occuId |
14+---------------------+--------+
15| Software Engineer   |      1 |
16| Economics           |      2 |
17| Structural Engineer |      3 |
18| Legal Advisors      |      4 |
19| Plumbers            |      5 |
20| Social Advisors     |      6 |
21| Musicians           |      7 |
22+---------------------+--------+
23	
24+---------------------+--------+
25| occuDscr            | amount |
26+---------------------+--------+
27| Software Engineer   |      2 |
28| Plumbers            |      1 |
29| Musicians           |      1 |
30+---------------------+--------+
31	
32select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount` 
33 from occupation_field a
34 INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
35 GROUP BY a.occuId;
36	
37mysql> select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount`
38    ->  from occupation_field a
39    ->  INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
40    ->  GROUP BY a.occuId;
41+--------+-------------------+--------+
42| occuId | occuDscr          | amount |
43+--------+-------------------+--------+
44|      1 | Software Engineer |      2 |
45|      5 | Plumbers          |      1 |
46|      7 | Musicians         |      1 |
47+--------+-------------------+--------+
483 rows in set (0.00 sec)
49	
50SELECT
51    oc.occuDscr,pst.amount
52FROM
53    (SELECT COUNT(1) amount,pstOccuId 
54    FROM job_post GROUP BY pstOccuId) pst
55    INNER JOIN occupation_field oc
56    ON (pst.pstOccuId = oc.occuId)
57ORDER BY
58    pst.amount DESC,oc.occuDscr
59;
60	
61mysql> use test
62Database changed
63mysql> drop table if exists occupation_field;
64Query OK, 0 rows affected (0.03 sec)
65
66mysql> drop table if exists job_post;
67Query OK, 0 rows affected (0.07 sec)
68
69mysql> create table occupation_field
70    -> (occuDscr varchar(50),
71    -> occuId int not null auto_increment,
72    -> primary key (occuId));
73Query OK, 0 rows affected (0.08 sec)
74
75mysql> insert into occupation_field (occuDscr)
76    -> values ('Software Engineer'),('Economics'),
77    -> ('Structural Engineer'),('Legal Advisors'),
78    -> ('Plumbers'),('Social Advisors'),('Musicians');
79Query OK, 7 rows affected (0.06 sec)
80Records: 7  Duplicates: 0  Warnings: 0
81
82mysql> create table job_post
83    -> (pstOccuId int not null,pstTitle varchar(50));
84Query OK, 0 rows affected (0.07 sec)
85
86mysql> insert into job_post values
87    -> (1,'Software Engineer Recruit'),
88    -> (1,'Web Developer Recruit'),
89    -> (7,'Saxophonist'),
90    -> (5,'Construction Company looking for plumber');
91Query OK, 4 rows affected (0.21 sec)
92Records: 4  Duplicates: 0  Warnings: 0
93
94mysql>
95	
96mysql> SELECT
97    ->     oc.occuDscr,pst.amount
98    -> FROM
99    ->     (SELECT COUNT(1) amount,pstOccuId
100    ->     FROM job_post GROUP BY pstOccuId) pst
101    ->     INNER JOIN occupation_field oc
102    ->     ON (pst.pstOccuId = oc.occuId)
103    -> ORDER BY
104    ->     pst.amount DESC,oc.occuDscr
105    -> ;
106+-------------------+--------+
107| occuDscr          | amount |
108+-------------------+--------+
109| Software Engineer |      2 |
110| Musicians         |      1 |
111| Plumbers          |      1 |
112+-------------------+--------+
1133 rows in set (0.00 sec)
114
115mysql>
116	
117SELECT 
118    oc.occuDscr,IFNULL(pst.amount,0) amount
119FROM
120    occupation_field oc LEFT JOIN
121    (SELECT COUNT(1) amount,pstOccuId  
122    FROM job_post GROUP BY pstOccuId) pst 
123    ON (oc.occuId = pst.pstOccuId) 
124ORDER BY 
125    pst.amount DESC,oc.occuDscr 
126;
127	
128mysql> SELECT
129    ->     oc.occuDscr,IFNULL(pst.amount,0) amount
130    -> FROM
131    ->     occupation_field oc LEFT JOIN
132    ->     (SELECT COUNT(1) amount,pstOccuId
133    ->     FROM job_post GROUP BY pstOccuId) pst
134    ->     ON (oc.occuId = pst.pstOccuId)
135    -> ORDER BY
136    ->     pst.amount DESC,oc.occuDscr
137    -> ;
138+---------------------+--------+
139| occuDscr            | amount |
140+---------------------+--------+
141| Software Engineer   |      2 |
142| Musicians           |      1 |
143| Plumbers            |      1 |
144| Economics           |      0 |
145| Legal Advisors      |      0 |
146| Social Advisors     |      0 |
147| Structural Engineer |      0 |
148+---------------------+--------+
1497 rows in set (0.00 sec)
150
151mysql>
152	
153SELECT occuDscr, COUNT(occuId) AS 'amount'
154 FROM occupation_field of,job_post jp
155 WHERE of.occuId = jp.pstOccuId
156   GROUP BY occuDscr, occuId
157   ORDER BY amount DESC;