Display parts of a query

Posted on 2006-05-03
Last Modified: 2013-12-24
       FORM                  OFFICE            TOTAL
1       CCR                    North                   10
2       CCR Closed         North                 2
3       Legislative           North                    2
4       RFA                    North                    43
5       RFA Closed         North                 9
6       CCR                    South                   25
7       CCR Closed         South                 8
8       RFA                     South                    31
9       RFA Closed         South                 9

i have a query that gives the above results.  what is the easiest way to display specific information without having to loop through each record?  for example, I would like to display the total CCR in North office.  And then, somewhere else on the page, I want to display the total RFA in the South office.  

is there a way I can just show the total where form=ccr and office=north and etc ..?

Question by:grizum
    LVL 18

    Accepted Solution



    Displays the first row, total field.

    LVL 10

    Assisted Solution

    you could query your db once then query that query for the individual offices.

    <cfquery name="getOfficeData" datasource="yourDSN">
      select form, office, total from yourTable

    <cfquery name="getNorth" dbtype="query">
       select total from getOfficeData
       where office = 'North'
       and form = 'CCR'

    <cfoutput>The north office CCR total:<br></cfoutput>

    <cfquery name="getRFA" dbtype="query">
      select total, office from getOfficeData
      where form = 'RFA'

    RFA Totals by office:<br>
    <cfoutput query="getRFA"> <br>

    Author Comment

    thanks guys .. pluka wrote exactly what i was wanting originally .. but i didnt realize u could query a query like that.  i may use that approach instead

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Article by: kevp75
    Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
    If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now