CODEWITHSUNDEEP
sqlsql-servert-sqljoingroup-by
nalux
nalux

Reputation: 19

Count function - SQL

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.

Upvotes: 1

Views: 70

Answers (1)

GMB
GMB

Reputation: 222492

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

Upvotes: 2

Related Questions