Results 1 to 5 of 5

Thread: Group by and order by

  1. #1
    Moderator
    Join Date
    Sep 2006
    Answers
    920

    Group by and order by

    Hi friends, i am executing this query in sql server 2005 select * from users where (primaryemail like 'brijesh%@efextra.com') order by firstname and it's working well.but when i tried this one select * from users where (primaryemail like 'brijesh%@efextra.com') group by firstname it's shows me error, why? i am not getting the reason

    Regards,
    Brijesh Jain
    ---------------------------------------------------------
    Connect with me on Skype: jainbrijesh
    Google Plus : jainbrijeshji

  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Group by and order by

    the GROUP BY clause does not contain all the expressions in the SELECT clause.


  3. #3
    Expert Member
    Join Date
    Oct 2007
    Answers
    375

    Re: Group by and order by

    Hi Brijesh,

    To use a Group BY clause, you would need to have atleast one aggregate function in the Select Clause.

    Aggregate function like Count(*), Sum, Avg etc.

    All other columns excepting the one with the Aggregate function, would need to be part of the GroupBy clause.

    Example.

    Select METHOD_NAME, Count(*), ROUND(AVG(LAST_CALL_DURATION)/1000,3), SERVICE_NAME, RESPONSE_STATUS
    From SGC.GATE_QUEUE
    where SYSTEM_NAME in ('CCTOOL', 'FALLOUT_HANDLING_TOOL') -- application of interest
    and date_modified >= to_date('10/03/2007 06:24 AM', 'MM/DD/YYYY HH:MI AM')
    and date_modified <= to_date('10/03/2007 07:45 AM', 'MM/DD/YYYY HH:MI AM')
    and METHOD_NAME = 'getPort25'
    Group By SERVICE_NAME, METHOD_NAME, RESPONSE_STATUS

    Cheers...


  4. #4
    Contributing Member
    Join Date
    Dec 2007
    Answers
    34

    Re: Group by and order by

    Hi,

    In Group by clause, its dont accept the text datatype. Pls chk whether ur one of the column may be a text.

    Thx
    Prakash


  5. #5
    Moderator
    Join Date
    Sep 2006
    Answers
    920

    Re: Group by and order by

    Quote Originally Posted by bizzzzzare View Post
    Hi Brijesh,

    To use a Group BY clause, you would need to have atleast one aggregate function in the Select Clause.

    Aggregate function like Count(*), Sum, Avg etc.


    Cheers...
    Thanks bizzzzzare,

    I got my answer.It's not required to use a Group BY clause, you would need to have atleast one aggregate function in the Select Clause.

    But the case is that, the column name which you are using in select should be either in group by statement or in aggregate function.

    Regards,
    Brijesh Jain
    ---------------------------------------------------------
    Connect with me on Skype: jainbrijesh
    Google Plus : jainbrijeshji

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact