SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast zeedoo's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting problems in sql

    SQL SERVER:

    i am trying to fetch the number of rows selected and the data columns in one query ...
    via this:
    select count(*) as cnt, * from employer group by eid

    where eid is numeric ...
    i get an error one by one for all fields in the table:

    Server: Msg 8120, Level 16, State 1, Line 1
    Column 'employer.ename' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    .
    .
    .

    How do i do that ?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the solve for this problem is listing the columns you get an error on in the GROUP BY clause. You would get something like:

    Code:
    select count(*) as cnt, * from employer group by eid, employer.ename, etc.

  3. #3
    SitePoint Enthusiast zeedoo's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    87
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that will be a tough solution, can't i just simply get the count and all values via one query without specifying all field names ?
    thanks

  4. #4
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you still get the error if you remove the AS clause?

    Goof
    Nathan Rutman
    A slightly offbeat creative.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You cannot do it that way.

    Why not use your host language to capture the "XX number of rows"? Most have the ability to do that, or you can use built-in variables such as @@rowcount


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •