2 Replies Latest reply on Aug 30, 2006 8:33 AM by <newbie />

    SQL Help

    Level 7
      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


        • 1. Re: SQL Help
          Dan Bracuk Level 5
          select field1, field2, etc, (select count(*) from some_other_table) abc
          from etc
          • 2. Re: SQL Help
            <newbie /> Level 1

            > 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!