Results 1 to 5 of 5

Thread: Total Losses

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Total Losses

    Hi I am trying to get a total losses by the month and I keep getting this error message, can someone tell me what I'm doing wrong??

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ','.

    Code:
    SELECT DISTINCT  ([Revised_MainTable].Date,'mmmm yyyy') AS [Date By Month], 
    [Revised_MainTable].[Violation Type], Sum([Revised_MainTable].Loss) AS [Sum Of Loss], Count([Revised_MainTable].[Violation Type]) AS [CountOfViolation Type])
    FROM dbo.Revised_MainTable
    GROUP BY ([Revised_MainTable].Date,'mmmm yyyy'), [Revised_MainTable].[Violation Type], Year([Revised_MainTable].Date)*12+DatePart('m',[Revised_MainTable].Date)-1
    HAVING ([Main Table].[Date],'mmmm yyyy')=[Enter the month and year])

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's this
    Code:
    ([Revised_MainTable].Date,'mmmm yyyy')
    Are you missing a function here?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the error occurs right after [Revised_MainTable].Date and just in front of the 'mmmm yyyy' string

    this sure don't look like a SQL Server query

    are you sure you're not looking to format a Microsoft Access date?

    if so, you forgot the Format function

    three other things: DISTINCT is not a function, your HAVING clause should be a WHERE clause (in fact, this is a dead giveaway that it's Access), and you mention two different tables in the query but there's only one in the FROM clause

    i think you have to go back to the drawing board

    Code:
    SELECT FORMAT([Date],'mmmm yyyy') AS [Date By Month]
         , [Violation Type]
         , SUM(Loss) AS [Sum Of Loss]
         , COUNT([Violation Type]) AS [CountOfViolation Type]
      FROM dbo.Revised_MainTable
     WHERE FORMAT([Date],'mmmm yyyy') = [Enter the month and year]
    GROUP 
        BY FORMAT([Date],'mmmm yyyy') 
         , [Violation Type]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First step in debugging any query...

    FORMAT IT!
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Oh great well that makes sense, I'm all mixed up. Thanks guys

Posting Permissions

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