Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Question Unanswered: Sql Help - Select Sum

    Hello,

    I have the following views and queries. What code can I use to incorporate the the SELECT SUM statements into the campus_3rdqtrcontr view? Currently I am keying in the actual values (1025 & 810) in this statement ROUND(CAMPUSTARGETS.QTR3/1025,2)*810 "CONTRIBUTION 3RD QTR". I would just like to reference the SELECT SUM statements for these values to save some time if possible. Ive tried but to no avail.

    - Thanks for your help.


    SQL> CREATE VIEW CAMPUS_TARGETS AS
    2 SELECT CAMPUSTARGETS.CAMPUSN, CAMPUSTARGETS.QTR3
    3 FROM CAMPUSTARGETS;

    View created.

    SQL> SELECT * FROM CAMPUS_TARGETS;

    CAMPUS QTR3
    ------ ---------
    Main 500
    East 250
    West 125
    North 150

    SELECT SUM(QTR3)
    FROM CAMPUSTARGETS;

    SUM(QTR3)
    ---------
    1025

    SQL> SELECT SUM(AMOUNT)
    2 FROM CONTRIBUTION
    3 WHERE CDATE > TO_DATE('80-06-03', 'YY-MM-DD')
    4 AND CDATE < TO_DATE('80-10-03', 'YY-MM-DD');

    SUM(AMOUNT)
    -----------
    810

    SQL> CREATE VIEW CAMPUS_3RDQTRCONTR AS
    2 SELECT CAMPUSTARGETS.CAMPUSN,
    3 ROUND(CAMPUSTARGETS.QTR3/1025,2)*810 "CONTRIBUTION 3RD QTR"
    4 FROM CAMPUSTARGETS;

    View created.

    SQL> SELECT * FROM CAMPUS_3RDQTRCONTR;

    CAMPUS CONTRIBUTION 3RD QTR
    ------ --------------------
    Main 396.9
    East 194.4
    West 97.2
    North 121.5

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    You could use 2 "dummy" inner joins in your view. The following code is just out of my head, as I don't have access to my computer right now. Should be something like this :

    CREATE VIEW CAMPUS_3RDQTRCONTR AS
    SELECT CAMPUSTARGETS.CAMPUSN,
    ROUND(CAMPUSTARGETS.QTR3/SUM_QTR3_CAMPUSTARGETS.QTR3)*SUM_QTR3_CONTRIBUTION S.QTR3 "CONTRIBUTION 3RD QTR"
    FROM CAMPUSTARGETS
    /* FIRST DUMMY INNER JOIN, ALWAYS RETURNS ONE, AND ONLY ONE ROW */
    INNER JOIN
    (SELECT SUM(QTR3) AS QTR3
    FROM CAMPUSTARGETS) SUM_QTR3_CAMPUSTARGETS ON 1 = 1
    /* SECOND DUMMY INNER JOIN ON CONTRIBUTION */
    INNER JOIN
    (SELECT SUM(AMOUNT) AS QTR3
    FROM CONTRIBUTION
    WHERE CDATE > TO_DATE('80-06-03', 'YY-MM-DD')
    AND CDATE < TO_DATE('80-10-03', 'YY-MM-DD')) SUM_QTR3_CONTRIBUTIONS ON 1 = 1
    ;

Posting Permissions

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