Thanks for a very easy to follow tutorial. Came in very handy. If I have a series of values in a database column, and want to sum these values - is there an easy way of doing this? I don't understand what Thomas Kreutzer wrote.
By: Mike Daugherty
Record Count of Database Results
This tutorial is designed to show you how to get a record count of the results returned by your database query. It’s very easy and has tons of useful applications.
First, lets pretend you have a database with demographic information for men and women living in San Deigo. Your fields are Sex, Age, Hair, EyeColor. You need to find out exactly how many females in San Deigo have blonde hair.
Now, query your database for that info.
<cfquery datasource="SanDemo" name="FemBlondes">
Select *
From Demographics
Where Sex = 'Female'
AND Hair = 'Blonde'
</cfquery>
To get the record count, simply do a
<cfoutput>#FemBlondes.recordcount#</cfoutput>
You can change the query to query anything you can think of, and the .recordcount will give you the total number of records. Along with that, you can use the cfchart tag to chart out your results.
For Example:
Same Database, multiple queries, all listed below:
<cfquery datasource="SanDemo" name="Men">
Select *
From Demographics
Where Sex = 'Male'
</cfquery>
<cfquery datasource="SanDemo" name="Women">
Select *
From Demographics
Where Sex = 'Female'
</cfquery>
<cfquery datasource="SanDemo" name="MenAge">
Select *
From Demographics
Where Sex = 'Male'
AND Age < '30'
</cfquery>
<cfquery datasource="SanDemo" name="WomenAge">
Select *
From Demographics
Where Sex = 'Female'
AND Age < '30'
</cfquery>
Now to chart the results in a bar graph:
<cfchart format="flash" scalefrom="1" scaleto="400" showxgridlines="no"
showygridlines="no" showborder="no" fontbold="no" fontitalic="no"
xaxistitle="Sex" yaxistitle="Count" show3d="yes" rotated="no"
sortxaxis="no" showlegend="no" showmarkers="no">
<cfchartseries type="bar" serieslabel="San Deigo Demographics" seriescolor="##0099FF">
<cfchartdata item="Men" value="#Men.recordcount#">
<cfchartdata item="Men Under 30" value="#MenAge.recordcount#">
<cfchartdata item="Women" value="#Women.recordcount#">
<cfchartdata item="Women Under 30" value="#Women Age.recordcount#">
</cfchartseries>
</cfchart>
The items in bold are just the record count results of your query. This is pretty simple folks but a nice tutorial none the less.
About This Tutorial
Author: Mike Daugherty
Total Views: 58,180
Submission Date: January 14, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 3
Skill Level: Beginner
Platforms Tested: CFMXTotal Views: 58,180
Submission Date: January 14, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 3
Discuss This Tutorial
-
-
That's a nice tutor for hepig me to develop my work, thanks.
-
I believe that you may want to use a case statement in this query. Here is an example.. Select sum((case when Sex = 'Female' then 1 else 0 end)) as totFemale, sum((case when Sex = 'Male' then 1 else 0 end)) as totMale, sum((case when Sex = 'Female' AND Age < '30' then 1 else 0 end)) as totFemaleAge, sum((case when Sex = 'Male' AND Age < '30' then 1 else 0 end)) as totMaleAge From Demographics
-
You could also use Query or queries to reduce db transactions; then run recordcounts from each of the recordsets.
-
I guess I shoud've included this with my original comment...to get the correct count with an aggregate query, use the Count(*) As VarName in your query and use the VarName as your counter. If there is no data that matches the query, you get a "0", otherwise you will get the count of the number of records that were used to satisfy the aggregate function.
-
Be careful when running "aggregate" queries (i.e., using Sum, Avg, etc.) and expecting a correct RecordCount. When running an aggregate query, RecordCount will always return a "1", whether there is data or not to match the query.
Advertisement
