sql count function

Question

Hi I have three tables and I want to know how much jobs with the wage of 1000 an employee has had

The first SQL query gives me the names of all the employees that has recieved 1000 for a job

SELECT distinct first_name 
FROM employee, job, link 
WHERE job.wage = 1000 
  AND job.job_id = link.job_id and employee.employee_id = link.employee_id;

The second SQL query gives me the total number for all employees of how much jobs they have made for 1000

SELECT count(wage) 
FROM employee, job, link 
WHERE job.wage = 1000 
  AND job.job_id = link.job_id and employee.employee_id = link.employee_id;

I was wondering if there was a way of joining both queries and also making the second for each specific employee???

Turbot · Accepted Answer · 2012-04-03 17:14:38Z

1

you might need to use group by keyword

SELECT count(wage), first_name
FROM employee, job, link 
WHERE job.wage = 1000 
  AND job.job_id = link.job_id and employee.employee_id = link.employee_id;
GROUP BY first_name

the drawback on this would be if there are different employee has the same first_name it would consider to the same count.

answered Apr 3, 2012 at 17:14

Turbot

5,2511 gold badge24 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Delavan · Accepted Answer · 2012-04-03 17:14:30Z

1

Something like below should work and you shouldn't need to the distinct first_name anymore.

select first_name, COUNT(wage) FROM employee, job, link  WHERE job.wage = 1000    AND job.job_id = link.job_id and employee.employee_id = link.employee_id
GROUP BY first_name

answered Apr 3, 2012 at 17:14

Bob Delavan

6475 silver badges6 bronze badges

Comments

Michael Fredrickson · Accepted Answer · 2012-04-03 17:25:14Z

1

I would also recommend changing your implicit joins to explicit joins:

SELECT employee.employee_id, first_name, last_name, count(wage) as wage_count
FROM 
    employee
    JOIN link ON employee.employee_id = link.employee_id
    JOIN job ON job.job_id = link.job_id
WHERE job.wage = 1000 
GROUP BY employee.employee_id, first_name, last_name

edited Apr 3, 2012 at 17:25

answered Apr 3, 2012 at 17:15

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Collectives™ on Stack Overflow

sql count function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related