SQL Help

[Newsgroup_User]

I need help with a SQL statement. What I would like to do in a single query
is count the records in one table and grab a number from another. I
attempted to write this:

SELECT Count(p.project_id) AS Total, m.projects
FROM project P, members M
WHERE p.dirname = m.username
AND m.dirname = '#arguments.username#'

I now realize I cannot have COUNT and a single varable in the same SQL
statement without a group by statement and I am lost. I want to count all
the project id's from one table and grab the column value from another
called 'projects' in the 'members' table.

I only want 2 items returned from the SQL statement: a number (Total) and a
the number from the project column of members.

Please help me.

--
Wally Kolcz
Developer / Support

[Dan_Bracuk]

select field1, field2, etc, (select count(*) from some_other_table) abc
from etc

[Guest]

> SQL statement without a group by statement

Any reason why you don't want to use a GROUP BY statement?

I'm not sure of the syntax but something like this...

SELECT Count(p.project_id) AS Total
, m.projects
FROM project P, members M
WHERE p.dirname = m.dirname
AND m.username = <cfqueryparam value="#arguments.username#" cfsqltype="CF_SQL_VARCHAR" />
GROUP BY m.projects


Good luck!