Display parts of a query

Posted on 2006-05-03
Medium Priority
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

Plucka earned 500 total points
ID: 16602341


Displays the first row, total field.

LVL 10

Assisted Solution

rob_lorentz earned 300 total points
ID: 16605706
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: #getNorth.total#<br></cfoutput>

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

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

Author Comment

ID: 16611942
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
What You Need to Know when Searching for a Webhost Provider
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

621 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