Skip to content
Advertisement

Syntax for counting greater than and summing a value with multiple joins without the keyword having

I’m learning SQL (Postgres) and I’m slightly confused about something I’m trying to do.

I have the following tables. Employee:

Project:

Department:

And works_on:

For this, I was trying to do something slightly more complex: To create a view that has project name, department name, number of employees, and total hours worked on each project that has the criteria of more than one employee on it. I got some reading and did some basic count(*) and sum(*) but the syntax here is killing me.

This is the furthest I got after many hours trying to understand how this syntax operates:

That WHERE clause is obviously wrong. I think I’ve got the JOINS correct. How do I put these aggregate functions correctly together with all these joins? Do I need nested queries? I’ve found some examples using HAVING but I heard it’s legacy, and would like to use things that are newer and fresher, but found no other examples except using GROUP BY and HAVING. Are those fine to use? If not, what should I use?

Please and thank you.

Advertisement

Answer

First, you need a GROUP BY clause to make your query a valid aggregation query: that clause should include all non-aggreated columns (I also added the project number, in case two projects have the same name).

Then, you can use a HAVING clause to filter on projects that have more than one employee:

If (essn, number) tuples are unique in the works table, then you don’t need DISTINCT in the COUNT():

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement