problem with count, group by and ntext

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Igor
    #1

    problem with count, group by and ntext

    I have one SELECT statement that needs to return one ntext field from
    one table and count something from other table, problem is that all
    fileds that are not in count have to be in group by and ntext can't be
    in group by... i hope you understend what i want to say here :), so is
    there any solution to this problem or what is the best workaraund you
    would use?

    example:
    TABLE projects
    project_id int
    project_title nvarchar(50)
    project_descrip tion ntext

    TABLE users
    user_id int
    project_id int

    SELECT projects.projec t_id, projects.projec t_title,
    projects.projec t_description, count(users.*) AS number_od_users
    FROM projects LEFT JOIN users ON projects.projec t_id=users.proj ect_id
    GROUP BY projects.projec t_id, projects.projec t_title,
    projects.projec t_description;

    this select is something that would work for me...if it wasn't for that
    tiny problem with ntext field

    Tags: None
    • Erland Sommarskog
      #2
      Re: problem with count, group by and ntext

      Igor (jerosimic@gmai l.com) writes:[color=blue]
      > I have one SELECT statement that needs to return one ntext field from
      > one table and count something from other table, problem is that all
      > fileds that are not in count have to be in group by and ntext can't be
      > in group by... i hope you understend what i want to say here :), so is
      > there any solution to this problem or what is the best workaraund you
      > would use?
      >
      > example:
      > TABLE projects
      > project_id int
      > project_title nvarchar(50)
      > project_descrip tion ntext
      >
      > TABLE users
      > user_id int
      > project_id int
      >
      > SELECT projects.projec t_id, projects.projec t_title,
      > projects.projec t_description, count(users.*) AS number_od_users
      > FROM projects LEFT JOIN users ON projects.projec t_id=users.proj ect_id
      > GROUP BY projects.projec t_id, projects.projec t_title,
      > projects.projec t_description;
      >
      > this select is something that would work for me...if it wasn't for that
      > tiny problem with ntext field[/color]

      SELECT p.project_id, p.project_title , p.project_descr iption,
      numbers_od_user s = coalesce(u.cnt, 0)
      FROM projects p
      LEFT JOIN (SELECT project_id, cnt = COUNT(*)
      FROM users
      GROUP BY project_id) AS u ON p.project_id = u.project_id

      The thing in parens is a derived table. Logically it is a temp table within
      the table, but it never materialised, and the actual computation order may
      be different. It is a very powerful tool to write SQL queries.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • Igor
          #3
          Re: problem with count, group by and ntext

          I wrote it like this:
          SELECT projects.projec t_id, projects.projec t_title,
          projects.projec t_description, (SELECT count(*) FROM users WHERE
          projects.projec t_id=users.proj ect_id) AS number_od_users
          FROM projects;

          what does "coalesce" do?

            Comment

            • Erland Sommarskog
              #4
              Re: problem with count, group by and ntext

              Igor (jerosimic@gmai l.com) writes:[color=blue]
              > I wrote it like this:
              > SELECT projects.projec t_id, projects.projec t_title,
              > projects.projec t_description, (SELECT count(*) FROM users WHERE
              > projects.projec t_id=users.proj ect_id) AS number_od_users
              > FROM projects;[/color]

              This is most likely slower than the version I suggested. Sub-selects in the
              SELECT list often gives poor performance.
              [color=blue]
              > what does "coalesce" do?[/color]

              coalesce takes list of values, and returns the first non-NULL value in the
              list, or NULL if all values are NULL.

              coalesce() is described in Books Online, if you want to read more.


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                • Igor
                  #5
                  Re: problem with count, group by and ntext

                  OK, thanks

                    Comment

                    Previous template Next
                    Working...