1 Reply Latest reply on Jun 8, 2007 2:13 PM by cf_dev2

    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
        • 1. Re: Query to come out the list
          cf_dev2 Level 1
          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