Professional Web Applications Themes

COUNT and GROUP - Coldfusion Database Access

I have been searching everywhere about using COUNT to display the specific recordcount of part of my query, but I can not figure it out for anything. I have been beating my head against the wall with this query. I am using Access and the database is relational. Below is what I would like to display: 05/06/01 student1 student2 student3 Subtotal: 91 (this should be 3) (I want to get a subtotal of the three students above displayed) 12/14/01 student1 Subtotal: 91 (this should be 1) (I want to get a subtotal of the one student above displayed) 06/03/02 student1 ...

  1. #1

    Default COUNT and GROUP

    I have been searching everywhere about using COUNT to display the specific
    recordcount of part of my query, but I can not figure it out for anything. I
    have been beating my head against the wall with this query. I am using Access
    and the database is relational.


    Below is what I would like to display:


    05/06/01
    student1
    student2
    student3

    Subtotal: 91 (this should be 3) (I want to get a subtotal of the three
    students above displayed)

    12/14/01
    student1

    Subtotal: 91 (this should be 1) (I want to get a subtotal of the one student
    above displayed)

    06/03/02
    student1
    student2
    student3

    Subtotal: 91 (this should be 3) (I want to get a subtotal of the three
    students above displayed)

    And Below is the code I have now. I didn't want to post what I have tried
    because I chopped it all up and it wasn't working anyhow:

    <cfquery name="qstudents" datasource="mydatabase">
    SELECT *
    FROM Students s
    INNER JOIN HoursLog h ON h.StudentID = s.StudentID
    WHERE h.Service = '#form.Service#' AND ServiceDate BETWEEN
    #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.Enddate)# ORDER BY
    ServiceDate, Service
    </cfquery>


    <table cellspacing="2" cellpadding="2" border="1">
    <cfoutput query="qstudents" group="ServiceDate">
    <tr>
    <td valign="top"><strong>#DateFormat(ServiceDate, 'mm/dd/yy')#</strong></td>
    </tr>
    <cfoutput>


    <tr>
    <td valign="top">#FirstName# #LastName#</td>
    </tr>

    </cfoutput>
    <tr>
    <td valign="top">Subtotal: #recordcount#</td>
    </tr>
    </cfoutput>
    </table>

    Thanks for any help in advance!


    brianism Guest

  2. #2

    Default Re: COUNT and GROUP

    This?

    <cfquery name="qstudents" datasource="mydatabase">
    SELECT s.ServiceDate, s.StudentID, count(*) AS subtotal
    FROM Students s
    INNER JOIN HoursLog h ON h.StudentID = s.StudentID
    WHERE h.Service = '#form.Service#'
    AND s.ServiceDate BETWEEN #CreateODBCDate(form.StartDate)# AND
    #CreateODBCDate(form.Enddate)#
    GROUP BY s.ServiceDate, s.StudentID
    ORDER BY s.ServiceDate, s.Service
    </cfquery>

    Phil

    paross1 Guest

  3. #3

    Default Re: COUNT and GROUP

    Use a variable to count rows. I'll call mine x.


    <cfoutput query="qstudents" group="ServiceDate">
    <cfset x = 0>
    <tr>
    <td valign="top"><strong>#DateFormat(ServiceDate, 'mm/dd/yy')#</strong></td>
    </tr>
    <cfoutput>
    <cfset x = x +1>
    <tr>
    <td valign="top">#FirstName# #LastName#</td>
    </tr>
    <tr>
    <td valign="top">Subtotal: #x#</td>
    </tr>
    </cfoutput>


    Dan Bracuk Guest

Similar Threads

  1. Order by results of count using 'group by'
    By salvador in forum MySQL
    Replies: 3
    Last Post: August 2nd, 06:49 PM
  2. Count GROUP BY query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: December 23rd, 07:03 AM
  3. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  4. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM
  5. Group by, count, type of query beyond my ability
    By Ray at in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 09:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139