CODEWITHSUNDEEP
mysqlsql
Jail
Jail

Reputation: 900

Counting the number of job orders per company

I need to write a query that returns the name of the company, and the number of the particular Job Orders that company owns.

Right now my query is like this:

SELECT c.name, cj.joborder_id
FROM company c, joborder jo, candidate_joborder cj
WHERE c.company_id=jo.company_id
AND jo.joborder_id=cj.joborder_id
AND jo.status = 'Active'
AND cj.status=700;

This returns the following table:

Name | Job Order ID
X    | 1874
Y    | 2003
Y    | 2003
Z    | 2001

What I want is:

Name | Count
X    | 1
Y    | 2
Z    | 1

Can someone help me with this?

Thanks

Upvotes: 0

Views: 66

Answers (3)

Menelaos
Menelaos

Reputation: 26279

The query you want is the following:

SELECT c.name,
       count(cj.joborder_id)
FROM company c,
     joborder jo,
     candidate_joborder cj
WHERE c.company_id=jo.company_id
  AND jo.joborder_id=cj.joborder_id
  AND jo.status = 'Active'
  AND cj.status=700
GROUP BY c.name;

I'd suggest the following references for SQL aggregation and specifically group by and count:

Upvotes: 2

Ryan
Ryan

Reputation: 28217

SELECT DISTINCT(c.name), COUNT(cj.joborder_id)
FROM company c, joborder jo, candidate_joborder cj
WHERE c.company_id=jo.company_id
AND jo.joborder_id=cj.joborder_id
AND jo.status = 'Active'
AND cj.status=700
GROUP BY c.name

Upvotes: 1

John Woo
John Woo

Reputation: 263803

use COUNT() and GROUP BY clause,

SELECT c.name, COUNT(cj.joborder_id) TotalCount
FROM   company c, joborder jo, candidate_joborder cj
WHERE  c.company_id=jo.company_id
       AND jo.joborder_id=cj.joborder_id
       AND jo.status = 'Active'
       AND cj.status=700
GROUP BY c.name

using ANSI JOIN

SELECT  c.name, 
        COUNT(cj.joborder_id) TotalCount
FROM    company c
        INNER JOIN joborder jo
            ON c.company_id = jo.company_id
        INNER JOIN candidate_joborder cj
            ON jo.joborder_id = cj.joborder_id
WHERE   jo.status = 'Active' AND 
        cj.status=700
GROUP   BY c.name

Upvotes: 2

Related Questions