count max values in postgresql

Question

I have a problem to formulate an sql question in postgresql, hoping to get some help here

I have three tables employee, visitor, and visit. I want to find out which employee (fk_employee_id) who have been responsible for most visit that haven't been checked out.

I want to make an sql question which are returning just the number one result, (by max function maybe?) instead of my current one, which are returning a ranked list (this ranked list doesn't work either if the number one position is shared by two persons)

This is my current sql question:

select visitor.fk_employee_id, count(visitor.fk_employee_id)

From Visit

Inner Join visitor on visit.fk_visitor_id = visitor.visitor_id
WHERE check_out_time IS NULL

group by visitor.fk_employee_id, visitor.fk_employee_id
Limit 1

Anyone now how to do this?

enter image description here

This question requires (at least) a table definition for `visit` (`CREATE TABLE` statement). And always your version of Postgres. — Erwin Brandstetter, Jul 01 '18 at 11:53
now I have attached an overview of mine tables in my question — Pete, Jul 01 '18 at 16:11
Your added ER diagram seems broken. Are you sure it's correct? Add the definition of table `visit` to make things clear. — Erwin Brandstetter, Jul 01 '18 at 16:18
Visit table PrimaryKey = visit_id visit_date visit_time_in visit_time_out check_in_time check_out_time ForeignKey = fk_employee_id (this is the person the visitor are going to meet) ForeignKey = fk_visitor_id Visitor table In the visitor table I have information about the person who are visiting the company and which employee who are the responsible for the meeting this day (fk_employee_id) I want to know which fk_employee_id in visitor who are responsible for the most meeting where check_out_time is null. — Pete, Jul 02 '18 at 06:31
All defining information in the *question*, please. Not in comments. Comments are too hard to read and lost to the general public. The preferred form for a table definition is a valid `CREATE TABLE` statement, showing data types and constraints. — Erwin Brandstetter, Jul 02 '18 at 15:08
Thant's good to hear. This is a public website, though. Questions are supposed to be useful to the general public, not only for you. — Erwin Brandstetter, Jul 02 '18 at 22:01

Shuwn Yuan Tee · Accepted Answer · 2018-07-02T07:44:49.233

0

To avoid confusion, I will change the column names to:

visitor table, the FK to employee id : employee_in_charge_id
visit table, the FK to employee id : employee_to_meet_id

From your explanation in comments, you are looking for Employee, who has the most visits which are not check-out .

In the case where, more than 1 employees are having same max number of visits which are not check-out, this query lists all the multiple employees:

SELECT * FROM 
(
    SELECT
        r.employee_in_charge_id,
        count(*) cnt,
        rank() over (ORDER BY count(*) DESC)
    FROM visit v
        JOIN visitor r ON v.visitor_id = r.id
    WHERE v.check_out_time IS NULL
    GROUP BY r.employee_in_charge_id
) a

WHERE rank = 1;

Refer SQLFidle link: http://sqlfiddle.com/#!17/423d9/2

Side Note:

To me, it sounds more correct if employee_in_charge_id is part of visit table, rather than visitor table. My assumption is for each visit, there is 1 employee (A) who is responsible to handle the visit, & the visitor is meeting 1 employee (B). So 1 visitor can make multiple visits, which handle by different employees.

Anyway, my answer above is based on your original schema design.

edited Jul 02 '18 at 07:44

answered Jul 01 '18 at 07:03

Shuwn Yuan Tee

5,578
6
28
42

Hi Thx for reply This sql working if you want to count the number one ranked fk_employee_id in visit, not in visitor In visit table I have a column called fk_visitor_id which is a foreign key to the table visitor, and the number of the meeting. Every meeting in the visitor table (every row) has a column called fk_employee_id which is the person who is responsible for the meeting, this is the one I want to find out the number one ranked. fk_employee_id in visit = the employee that will meet the visitor fk_employee_id In visitor = the employee that are responsible for the meeting – Pete Jul 01 '18 at 08:34
Please show your table schema definition in question then. – Shuwn Yuan Tee Jul 01 '18 at 08:40
Now I have attached my table schema in my question, probably should have done it directly instead of trying to explain in text ;-) – Pete Jul 01 '18 at 16:05
@Pete From my understanding, an `employee` is responsible for a `visitor`. A `visitor` make several `visit`. So why you still need `fk_employee_id` in `visit` table? Aren't this relation is already specified in `visitor` table? Still not clear to me, please provide sample data with expected output. – Shuwn Yuan Tee Jul 02 '18 at 03:44
Hi. The employee (fk_employee_id) have two function in my tables. In the visit table it show which employee the visitor are going to meet, and in the visitor table it is the employee who are responsible for the visit, they don't have to be the same employee. Ex. Carl from Siemens is visiting my company X to meet Johan who works there (fk_employee in visit). This day Adam is the responsible for all the meeting in our company (fk_employe_id In visitor). So I want to find out which fk_employee_id in visitor who are responsible for the most meeting that haven't been checked out.. – Pete Jul 02 '18 at 06:21

score 0 · Answer 2 · answered Jul 01 '18 at 12:03

0

Assuming a standard n:m implementation like detailed here, this whould be one way to do it:

SELECT fk_employee_id
FROM   visit
WHERE  check_out_time IS NULL
GROUP  BY fk_employee_id
ORDER  BY count(*) DESC
LIMIT  1;

Assuming referential integrity, you do not need to include the table visitor in the query at all.

count(*) is a bit faster than count(fk_employee_id) doing the same in this case. (assuming fk_employee_id is NOT NULL). See:

PostgreSQL: running count of rows for a query 'by minute'

answered Jul 01 '18 at 12:03

Erwin Brandstetter

605,456
145
1,078
1,228

Hi. Thx for comment. the one I try to count is in visitor (fk_employee_id) so I have to include that table also. (See my diagram I attached to the question) So I assume I have to write som kind of join statement to solve it – Pete Jul 01 '18 at 16:09

count max values in postgresql

2 Answers2