Solved

SQL count and and SUM

Posted on 2010-09-17
3
532 Views
Last Modified: 2013-12-07
Hi There,

I am trying to work out this query in POSTGRESQL database.  I need to list all workers that have done more than 3 jobs. That part of query works fine. It lists all workers with more than 3 jobs in the 'job' table. The job table lists worker_id with every job that was done so I am simply counting the number of times worker_id occurs and then list only those that are listed more then 3 times. What I need now is to add a column that would give me a total number of jobs done by those workers as well.

So the result set would be something like:

John Smith 35
Bill Cosby 40
Dan White 4

SELECT person.first_name, person.surname
FROM person INNER JOIN job on (person.person_id = job.worker_id)
GROUP BY person.first_name, person.surname
HAVING COUNT(job.worker_id) > 3
0
Comment
Question by:gosvald
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Accepted Solution

by:
kannanbabu earned 125 total points
ID: 33699720
SELECT person.first_name, person.surname, count(job.worker_id) as Job_Count
FROM person INNER JOIN job on (person.person_id = job.worker_id)
GROUP BY person.first_name, person.surname
HAVING COUNT(job.worker_id) > 3
0
 
LVL 1

Assisted Solution

by:sudheerg
sudheerg earned 125 total points
ID: 33699758
Hi ,

Just add the column count(job.worker_id) in the select clause like so:

SELECT person.first_name, person.surname
FROM person INNER JOIN job on (person.person_id = job.worked_id)
GROUP BY person.first_name, person.surname
HAVING count(job.worked_id)>3

You'll get what u need.
0
 
LVL 2

Author Comment

by:gosvald
ID: 33699910
Thanks guys. I actually had this already but I was getting errors. Then I realized that case was wrong (POSTGRESQL is case sensitive)!
Anyway. Thanks for the help.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question