Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Order by Date not working in SQL 2005

    Code:
    SELECT COUNT(*) AS Orders, CONVERT (VARCHAR(10), order_date, 103) AS Expr1 FROM tblFixedLine_order_summary WHERE (order_date BETWEEN GETDATE() - 100 AND GETDATE()) AND (status IS NULL) GROUP BY CONVERT (VARCHAR(10), order_date, 103) ORDER BY MONTH(CONVERT (VARCHAR(10), order_date, 103))
    The code above works fine locally using VS2005 but when i upload this to my liver server which i believe is SQL 2005 ....

    Column name 'tblFixedLine_order_summary.order_date' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - exactly that. Stick the expression in the group by clause.

    BTW - are you absolutely certain you want to convert your dates to strings?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2009
    Posts
    32
    Quote Originally Posted by pootle flump
    Yeah - exactly that. Stick the expression in the group by clause.

    BTW - are you absolutely certain you want to convert your dates to strings?
    All i want to do is show the order_date with a count next to it, Grouped and ordered by date but i cant seem to get it right. This is the closest i have got in that it works locally!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  COUNT(*) AS Orders
          , order_date
    FROM    tblFixedLine_order_summary
    WHERE   order_date BETWEEN DATEADD(d, - 100, GETDATE()) AND GETDATE()
            AND status IS NULL
    GROUP BY order_date
    ORDER BY order_date
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT DATEADD(D,DATEDIFF(D,0,GETDATE()),0) AS my_date
         , COUNT(*) AS Orders
      FROM tblFixedLine_order_summary 
     WHERE order_date BETWEEN GETDATE() - 100 AND GETDATE()
       AND status IS NULL
    GROUP 
        BY my_date
    grouped by the date, pootsie, not by the datetime

    group by the datetime and you're likely to get only one order per group

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I was waiting for the response "but there's times in my dates" before going along the time-stripping route. But you shortcutted me

    So how do you "right align" your keywords? Do you put two spaces before FROM and one before WHERE, or is it something more automated?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, I did forget to strip time off GETDATE(), but so did you so that gets me off the hook....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Actually, I did forget to strip time off GETDATE(), but so did you so that gets me off the hook....
    stripping the time off the order date for the GETDATE()-100 calculation may or may not be required, and this depends on user requirements

    the user requirement to get one total per day, though, pretty much demands the stripping

    (heh, i said stripping)

    as for formatting the SQL, yes, i use spaces
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    stripping the time off the order date for the GETDATE()-100 calculation may or may not be required, and this depends on user requirements
    I meant stripping the time portion off GETDATE() as well. But yes you are right - we need the OP to tell us if we should strip or not
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    @poots: Tools > Options > Text Editor > All Languages > Tabs > Insert Spaces

    I hate tabs because they do not display consistantly between programs/computers/servers/environments/etc - spaces FTW
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That was for SSMS ^^

    For QA: Tools > Options > Editor > Save tabs as spaces

    EDIT: I now realise you use spaces now anyway I could hae sworn you used to be a tabber
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee
    EDIT: I now realise you use spaces now anyway I could hae sworn you used to be a tabber
    Correct - My name is pootle and I used to be a tabber. In fact, you werer the one that put me on to the "use spaces" trick.
    It was specifically the right aligning of the clause keywords I was enquiring about from Rudy Tuesday.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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