Count function - SQL

Question

I have these practice tables to work with:

create table complaints (compid int, compdate date, fineid int, description varchar)
insert into complaints values
(1010, '24-FEB-2017', 10001, NULL),
(1011, '01-AUG-2017', 10003, NULL),
(1012, '30-JUL-2017', 10004, NULL),
(1013, '02-MAR-2017', 10001, NULL)

create table Fines (fineID int, finedate date, empid int, amount int)
insert into Fines values
(10001, '01-FEB-2017', 1, 250),
(10002, '11-MAR-2017', 2, 250),
(10003, '25-JUN-2017', 4, 500),
(10004, '23-JUL-2017', 4, 250)
(10005, '31-JUL-2017', 3, 250)

create table Employees (empid int, empname nvarchar(100), workingsince date)
insert into Employees values
(1,'John','01-JAN-2007'),
(2,'Abe','01-JUL-2008'),
(3,'Don','01-JAN-2013'),
(4,'Gon','01-JAN-2017')

Now, I want to create a query that will show me for each employee name, the number of fines and the number of complaints (all in the same table - empname, NumofFines, NumOfComp).

It seems I need 2 columns with the count function and I can't find a way to do it.

What are the primary keys of all tables involved? Also the data model seems wrong. An employee can have zero to many fines. And each fine can have zero to many complaints but there may be a fine with out any corresponding complaint? So what is the fine for? — Martin Smith
– Martin Smith, Commented Apr 15, 2020 at 19:06
Employee can give 0 to many fines, each fine can have 0 to many compliants. And yes, there can be a fine which was given by employee and has no complaints. Or maybe I didn't understand your question. — nalux
– nalux, Commented Apr 15, 2020 at 19:29
Right - I understand. I though the fines were being received by employees in response to complaints. I now see that they are being issued by employees and may result in a complaint — Martin Smith
– Martin Smith, Commented Apr 15, 2020 at 19:43

GMB · Accepted Answer · 2020-04-15 18:42:08Z

2

You can use aggregation:

select 
    e.empname,
    count(distinct f.fineid) no_fines,
    count(distinct c.compid) no_complaints
from employees e
left join fines f      on f.empid = e.empid
left join complaints c on c.fineid = f.fineid
group by e.empid, e.empname

Demo on DB Fiddle:

empname | no_fines | no_complaints
:------ | -------: | ------------:
Abe     |        1 |             0
Don     |        1 |             0
Gon     |        2 |             2
John    |        1 |             2

edited Apr 15, 2020 at 18:42

answered Apr 15, 2020 at 18:36

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

GMB Over a year ago

@EduardoAlmeida: that's in case there is more than one fine for a given complaint.

Martin Smith Over a year ago

Doesn't seem like that should be possible. Presumably compid is PK of complaints - so each complaint is associated with exactly one fine per the fineid column

Collectives™ on Stack Overflow

Count function - SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related