Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Wrong with query!

    select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS
    , SUM(inv_numb, freight_val)
    FROM OPCSAHH
    WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND
    LESS THEN = 2003
    AND PREPAID COLLECT FLAG = 'C'




    What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.

    anyone know?

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Re: Wrong with query!

    Originally posted by estefex
    select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS
    , SUM(inv_numb, freight_val)
    FROM OPCSAHH
    WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND
    LESS THEN = 2003
    AND PREPAID COLLECT FLAG = 'C'




    What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.

    anyone know?
    Try this: With the aggregate Sum() the other fields need a group by clause.

    select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS
    , SUM(inv_numb, freight_val)
    FROM OPCSAHH
    WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND
    LESS THEN = 2003
    AND PREPAID COLLECT FLAG = 'C'
    Group by DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would think...

    Code:
      SELECT    DATEPART(YEAR,DATE_CUST_INVOICE)
    	, PREPAID_COLLECT_FLAG
    	, COUNT(*) AS [COUNT OF RECORDS]
    	, SUM(inv_numb)
    	, SUM(freight_val)
        FROM  OPCSAHH
       WHERE  DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' 
         AND  DATEPART(YEAR, DATE_CUST_INVOICE) < = '2003'
         AND  PREPAID_COLLECT_FLAG = 'C'
    GROUP BY  DATEPART(YEAR,DATE_CUST_INVOICE)
    	  PREPAID_COLLECT_FLAG
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2004
    Posts
    164
    Thanks alot. the group by function was what I was missing like you said. Here is what I ended up with:

    select DATEPART(YEAR,DATE_CUST_INVOICE) as Inv_Year, PREPAID_COLLECT_FLAG, sum(freight_val)as Total_Freight_Val, Count (*) as Total_Records
    FROM OPCSAHH
    WHERE DATEPART(YEAR, DATE_CUST_INVOICE) Between 2000 AND 2003
    AND PREPAID_COLLECT_FLAG = 'C'
    Group by DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG
    order by Inv_Year

    Thanks oce again,

    Estefex

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    LESS THEN!??

    That isn't even grammatically correct, let alone syntactically correct!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    LESS THEN!??

    That isn't even grammatically correct, let alone syntactically correct!
    very helpful
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Not to mention the two-argument SUM.
    -bpd

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or the three-part column name: PREPAID COLLECT FLAG

    Somebody needs to spend some time reading Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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