from tblClientDailes cd
inner join tblClients c on c.clientName = cd.clientName
where cd.dailliesDate between '#date1#' and '#date2#'
and c.acctStatus = 'A'
and cd.clientName like '%marsh%'
group by c.clientName
try that.
couple things though in your db design, that I see that I would change.
I would use an id back and forth between the two tables, rather than
clientName
being the join column, very slow to seek on text, vs. an id column.
I would have a boolean field for acctStatus, again, faster than text. and
you can
deduce the same state of the acctStatus, from a 1 or 0. active or
non-active.
anyway, try that.
tw
-----Original Message-----
From: hammerin hankster [mailto:[EMAIL PROTECTED]
Sent: Friday, May 28, 2004 9:56 AM
To: CF-Talk
Subject: SUM column data
I'm trying to SUM a column of data but I'm missing something obvious in my
concept here. Also, I hope this is the right list to post this question to.
If not, please let me know and I'll repost to the proper list. Below is my
query:
<CFQUERY NAME="readpostage" DATASOURCE="esitest">
SELECT postagecost,
SUM(postagecost) AS totalpostage
FROM tblclientdailies, tblclients
where tblclients.clientname = tblclientdailies.clientname
and tblclients.acctstatus = 'A'
and dailiesdate BETWEEN #Date1# AND #Date2#
and tblclientdailies.clientname like '%marsh%'
GROUP BY postagecost, dailiesdate
order by dailiesdate
</CFQUERY
Here's the first 5 rows of data in the column:
.37
.37
.37
.00
.37
And here's what the <cfoutput> is returning (first five rows):
1.1100
0.3700
0.0000
1.1100
1.2000
See anything obviously wrong?
Thanks!
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
