Query to come out the list

[clixmax]

I hope somebody can help me to produce the output as shown below:

ID NAME COURSE REC OF R2001
___________________________________________________________________________
16DAT03F501 NUR ZAYANA BINTI MD YUSOF DAT 2
16DAT03F502 AZLIZA BINTI ZAINAL DAT 1

My problem is, i have no idea to get the output at the last column. I want the total output (REC OF R2001) for each ID. Let say, NUR ZAYANA have 2 records of R2001 and the list should show the total of her record for R2001.

The database design is something like this:

16DAT03F501 NUR ZAYANA BINTI MD YUSOF DAT1 R2001
16DAT03F501 NUR ZAYANA BINTI MD YUSOF DAT1 R2001
16DAT03F502 AZLIZA BINTI ZAINAL DAT1 R2001

[cf_dev2]

It sounds as if you need to use an aggregate function like COUNT(). Either something like

SELECT ID, NAME, COURSE, COUNT(*) AS NumberOfRecords
FROM YourTable
GROUP BY ID, NAME, COURSE

... OR ...
SELECT ID, NAME, COURSE, COUNT(NameOfR2001Column) NumberOfRecords
FROM YourTable
GROUP BY ID, NAME, COURSE