Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Need Help Creating User Defined Fields

    I thought I knew how to do this, but apparently I have some syntax wrong.

    I am trying to create a report that will sum up total payments per year so I would have a user defined sum field called 2006, 2007, and so on.

    This query does create the sum for 2006:

    SELECT SUM(ANTY_PYMT_TOT_AMT) AS "2006"
    FROM DSNP.PR01_T_ANTY_PYMT
    WHERE ANTY_PYMT_DT BETWEEN '2006-01-01' AND '2006-12-31'


    However, SQL doesn't like it when I try to nest this query here:

    SELECT A.2006 TOTAL

    FROM

    (SELECT SUM(ANTY_PYMT_TOT_AMT) AS "2006 TOTAL"
    FROM DSNP.PR01_T_ANTY_PYMT
    WHERE ANTY_PYMT_DT BETWEEN '2006-01-01' AND '2006-12-31') AS A



    I get an error complaining about A.2006. Any idea what I'm doing wrong?

  2. #2
    Join Date
    Jan 2009
    Posts
    46
    nevermind guys. It is working now.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm [guessing you] FiguredOut what [The.Problem] was.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by blindman
    I'm [guessing you] FiguredOut what [The.Problem] was.

    SQL has just been really kicking my butt this week. My query has morphed into this:

    SELECT DISTINCT A.RECIP_SSN_NBR,

    B.MBR_L_NM,
    D.TOTAL_2006,
    E.TOTAL_2007



    FROM DSNP.PR01_T_RECIP_SYS A,
    DSNP.PR01_T_MBR B,

    (SELECT RECIP_SSN_NBR, SUM(ANTY_PYMT_NET_AMT) AS TOTAL_2006
    FROM DSNP.PR01_T_ANTY_PYMT
    WHERE ANTY_PYMT_DT BETWEEN '2006-01-01' AND '2006-12-31'
    GROUP BY RECIP_SSN_NBR) AS D,

    (SELECT RECIP_SSN_NBR, SUM(ANTY_PYMT_NET_AMT) AS TOTAL_2007
    FROM DSNP.PR01_T_ANTY_PYMT
    WHERE ANTY_PYMT_DT BETWEEN '2007-01-01' AND '2007-12-31'
    GROUP BY RECIP_SSN_NBR) AS E


    Anyone see a logic problem here? The query works great when I run the sub-queries by themselves. Also it worked when I built in the sub-query for 2006. When I added the sub-query for 2007, I did not get an accurate sum. I received numerous rows for the same SSN with different dollar amounts. I should only get one record per SSN.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you haven't joined any of the 4 tables in the FROM clause properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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