a query : this one stops me

Hi again,

I was practicing queries and this one I tried to find how to write it but I can't find how to do it.


I think a single table is enough.

The table :

CREATE TABLE COURSE
( NUMCOURSE NUMERIC(4) PRIMARY KEY,
DATECOURSE DATETIME,
NUMSAL NUMERIC(2) FOREIGN KEY REFERENCES SALARIE (NUMSAL),
NUMCLI CHAR(6) FOREIGN KEY REFERENCES CLIENT (NUMCLIENT),
LIEUDEPART CHAR(15),
LIEUARRIVE CHAR(3));

Here is how it is filled :

INSERT INTO COURSE VALUES ('1612','08-16-2002','1','PAR002','LYON','LYON')
INSERT INTO COURSE VALUES ('1613','08-17-2002','5','ADM002','LYON','PARIS')
INSERT INTO COURSE VALUES ('1614','08-18-2002','3','ENT004','VALENCE','LYON')
INSERT INTO COURSE VALUES ('1615','08-19-2002','2','ENT006','LYON','DIE')
INSERT INTO COURSE VALUES ('1616','08-20-2002','1','ADM005','LYON','LYON')
INSERT INTO COURSE VALUES ('1617','08-21-2002','4','PAR007','LYON','LYON')
INSERT INTO COURSE VALUES ('1618','08-22-2002','4','ADM006','LYON','MARSEILLE')
INSERT INTO COURSE VALUES ('1619','08-23-2002','2','ENT008','MONTELIMAR','LYON')
INSERT INTO COURSE VALUES ('1620','08-24-2002','4','ADM008','MARSEILLE','LYON')


The QUERY is : count the number of courses performed by the employe number 2 (the number being NUMSAL).

I don't know how to write as it has to filter who performed the courses before counting. And, according to what I was told the COUNT must be written in the SELECT.

Thank you for your help.

Thank you !

I was also suggested :

The most obvious answer would be

select count(*)
from Course
where numsal = 2

If you need to 'filter' first, then this might work (makes no sense why you'd do it)

select count(*)
from (select * from course where numsal = 2) as X

Your second example using a derived table is very useful when you need to count items which rely on joining several tables and complicated filters. If you don't use a derived table, you have to mess around with grouping and are likely to run into errors.



This way you can make the selection of rows as complicated as you need to and then simply count the number of rows returned.