Thanks Dave!
Here is your final code:
#story.fdd_name#
SELECT fdcount
FROM zip
WHERE zip.fdd_id =
(#NumberFormat(qZipCheck.fdcount)#)
Awesome.
Barry
~|
Want to reach the ColdFusion community
, I assumed your fdd_id is an integer, if it's not,
use cf_sql_varchar for the cfsqltype value instead.
Dave
-Original Message-
From: Barry Mcconaghey [mailto:bmcconag...@gmail.com]
Sent: Tuesday, February 16, 2010 7:53 AM
To: cf-talk
Subject: Re: Total Two Field From Two Tables
Good Morning.
This code works below but I'm not sure if there is a better way. Please let me
know.
SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, f.fdd_id, count(f.fdd_id)
AS FDCount
FROM articles a, fddirectory f
WHERE a.zipcode = LEFT(f.fdd_zip, 5)
AND f.fdd_state =
GROUP BY
Hi Dave.
I fixed the last error message. There was a space here:
name=" qZipTotals "
Now the data is displaying but it is not correct.
This code below (ZIP)is correct:
>
>
>SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, count(f.fdd_id) AS
>FDCount
>FROM articles a, fddirectory f
>WHERE
Here is the error message:
The string qZipTotals is not a valid ColdFusion variable name.
Valid variable names must start with a letter and can only contain letter,
numbers, and underscores.
Barry
~|
Want to reach the ColdF
PM
To: cf-talk
Subject: Re: Total Two Field From Two Tables
Hi Dave.
Thanks for the code!
Those two did not work.
I got two queries to work perfect but I don't know how to display them.
SELECT F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev
FROMfddirectory F, states S
WHER
Hi Dave.
Thanks for the code!
Those two did not work.
I got two queries to work perfect but I don't know how to display them.
SELECT F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev
FROMfddirectory F, states S
WHERE F.fdd_state =
AND F.fdd_state = S.abrev
ORDER BY fdd_na
Barry,
Try this:
SELECT a.zipcode, f.fdd_zip, f.fdd_name, a.IsPublished, a.IsActive,
f.fdd_state, count(distinct a.zipcode) as zip_count
FROM articles a, fddirectory f
WHERE a.zipcode = f.fdd_zip
AND f.fdd_state =
GROUP BY a.zipcode, f.fdd_zip, f.fdd_name, a.IsPublished, a.IsActive,
f.fd
I think I'm getting closer. This code below works but I don't know how to total
each fdd_name.
For Example:
ACME FD1 (38)
ACME FD2 (30)
ACME FD3 (20)
SELECT a.zipcode, f.fdd_zip, f.fdd_name, a.IsPublished, a.IsActive, f.fdd_state
FROM articles a, fddirectory f
WHERE a.zipcode = f.fdd_zip
Thanks Barney!
The JOIN and GROUP clause might be over my head.
Do I use a RecordCount?
Barry
>You just need a JOIN and GROUP BY clause. Check out the
>subquery-to-join docs on MySQL's site. They have examples of how to do
>exactly what you want.
>
>cheers,
>barneyb
You just need a JOIN and GROUP BY clause. Check out the
subquery-to-join docs on MySQL's site. They have examples of how to do
exactly what you want.
cheers,
barneyb
On Mon, Feb 15, 2010 at 11:19 AM, Barry Mcconaghey
wrote:
>
> I'm using MySQL 4.0.20.
>
> Is there any other way to total these
I'm using MySQL 4.0.20.
Is there any other way to total these two fields?
Barry
>What version of MySQL are you using? If it's a really old one that
>doesn't support subqueries, check out the MySQL docs. They have a
>section about rewriting subqueries into JOINs.
>
>cheers,
>barneyb
What version of MySQL are you using? If it's a really old one that
doesn't support subqueries, check out the MySQL docs. They have a
section about rewriting subqueries into JOINs.
cheers,
barneyb
On Mon, Feb 15, 2010 at 9:16 AM, Barry Mcconaghey wrote:
>
> Thanks Barney B.
>
> ERROR Message:
Thanks Barney B.
ERROR Message:
Syntax error or access violation: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right syntax
to use near 'select count(*) from articles where zipcode = fddirectory.fdd_z
Here is the cfquery:
SELEC
Just add:
(select count(*) from articles where zipcode = fddirectory.fdd_zip) as
articleCount
to the end of your SELECT clause:
SELECT fddirectory.fdd_id, fddirectory.fdd_name, fddirectory.fdd_zip,
fddirectory.fdd_state, states.abrev
, (select count(*) from articles where zipcode = fddirectory
Hello Everybody.
I have been thinking and working on this for three days now. I'm looking for
some help totaling two fields from two tables.
Here is what I have:
Sample Tables:
Table - fdd_directory
fdd_name (Example: ACME Fire Dept)
fdd_state (Example: PA)
fdd_zip (Example: 12345)
Table -
16 matches
Mail list logo
