Hello, I have the following view for the 3rd quarter I need to include all 4 quarters in this view. The only thing that will change is the qtr name (ie qt1, qtr2 etc) the sums and the date ranges, the % of target achieved will need to be at the end and calcuate all months. How can I join each of these queries for all quarters into one big one that will output each quarter in the view, ive tried but keep getting errors? - thanks for your help

SQL> CREATE VIEW campuscont_qtr3 AS
2 SELECT campus.campus,
3 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
4 (ROUND(campus.QTR3/tot_contr,2)*q3)/
5 campus.QTR3*100 "% OF TARGET ACHIEVED"
6 FROM campus,(SELECT SUM(AMOUNT) Q3
7 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
8 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
9 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr;

CAMPUS QTR3 % OF TARGET ACHIEVED
------ ---------- --------------------
Main 396.9 79.38
East 194.4 77.76
West 97.2 77.76
North 121.5 81

You can make use of the UNION / UNION ALL clause

CREATE VIEW CAMPUS_CONTR_ALL_QTR
AS

SELECT
1 AS QTR,
...the rest of your select concerning the first quarter

UNION ALL

SELECT
2 AS QTR,
...the rest of your select concerning the second quarter

and so on.

OK. looks like this will work but IM getting the wrong output. I would only like one instance of campus and all the quarters to follow. Heres what the output I receieved.

SQL> SELECT campus.campus,
2 ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3"
3 FROM campus,(SELECT SUM(AMOUNT) Q3
4 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
5 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q3contr,
6 (SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
7 UNION ALL
8 SELECT campus.campus,
9 ROUND(campus.QTR2/tot_contr,2)*q2 "QTR2"
10 FROM campus,(SELECT SUM(AMOUNT) Q2
11 FROM contribution wHERE CDATE >= TO_DATE('07/01/03', 'MM/DD/YY')
12 AND CDATE <= TO_DATE('09/30/03', 'MM/DD/YY')) Q2contr,
13 (SELECT SUM(QTR2) Tot_Contr FROM campus) totcontr;

CAMPUS QTR3
------ ----------
Main 396.9
East 194.4
West 97.2
North 121.5
Main 380.7
East 234.9
West 97.2
North 97.2

Let's get this straight. The UNION of selects should have returned the following result :

CAMPUS QTR CONTRIBUTION
====================
Main 1 396.9
East 1 194.4
West 1 97.2
North 1 121.5
Main 2 380.7
East 2 234.9
West 2 97.2
North 2 97.2

Now what you actually would like is something like
CAMPUS QTR1 QTR2 QTR3
=====================
Main 396.9 380.7
East 194.4 234.9
West 97.2 97.2
North 121.5 97.2

Is that what you want ?

BTW, I assume that the date range in your latest select for Q2 is a typo...

Yes, just what I wanted. How would I do it?
- thanks

OK. To achieve this, your first query is looking good, and should be modified only slightly.

I can only suggest to use the INNER JOIN, LEFT OUTER JOIN, etc. syntax. It makes the query much easier to read.

Your final select would look like this

SELECT campus.campus,
ROUND(campus.QTR3/tot_contr,2)*q3 "QTR3",
(ROUND(campus.QTR3/tot_contr,2)*q3)/
campus.QTR3*100 "% OF TARGET ACHIEVED"

/* CAMPUS IS YOUR DRIVING TABLE */
FROM campus

/* inner join on 1st qtr contribution, select SUM always returns 1 row */
INNER JOIN
(SELECT SUM(AMOUNT) Q1
FROM contribution wHERE CDATE >= TO_DATE('01/01/03', 'MM/DD/YY')
AND CDATE <= TO_DATE('31/03/03', 'MM/DD/YY')) Q1contr
ON 1 = 1

/* inner join on 2ND qtr contribution, select SUM always returns 1 row */
INNER JOIN
(SELECT SUM(AMOUNT) Q2
FROM contribution wHERE CDATE >= TO_DATE('01/04/03', 'MM/DD/YY')
AND CDATE <= TO_DATE('30/06/03', 'MM/DD/YY')) Q2contr
ON 1 = 1

etc..

/* inner join on total contribution */
INNER JOIN
(SELECT SUM(QTR3) Tot_Contr FROM campus) totcontr
ON 1 = 1;



Just out of curiosity. Isn't there any relationship between "Contributions" and "Campus" (like a CAMPUS column in table "Contributions") ?