SELECT V.SNO, AVG(V.CHARGES)
FROM CUST, TABLE (
SELECT SNO, CHARGES, DATE
FROM MONTH1
WHERE DATE BETWEEN '01/01/2000' AND '01/31/2000'
UNION ALL
SELECT SNO, CHARGES, DATE
FROM MONTH2
WHERE DATE BETWEEN '02/01/2000' AND '02/29/00'
UNION ALL
SELECT SNO, CHARGES, DATE
FROM MONTH3
WHERE DATE BETWEEN '03/01/2000' AND '03/31/2000'
) AS V(SNO, CHARGES, DATE)
WHERE CUST.SNO=V.SNO
AND CUST.STATE='CA'
AND DATE IN ('01/28/2000','02/25/2000','03/31/2000')
GROUP BY V.SNO
Is this syntax not supported yet?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
SELECT V.SNO, AVG(V.CHARGES)
FROM CUST, TABLE (
SELECT SNO, CHARGES, DATE
FROM MONTH1
WHERE DATE BETWEEN '01/01/2000' AND '01/31/2000'
UNION ALL
SELECT SNO, CHARGES, DATE
FROM MONTH2
WHERE DATE BETWEEN '02/01/2000' AND '02/29/00'
UNION ALL
SELECT SNO, CHARGES, DATE
FROM MONTH3
WHERE DATE BETWEEN '03/01/2000' AND '03/31/2000'
) AS V(SNO, CHARGES, DATE)
WHERE CUST.SNO=V.SNO
AND CUST.STATE='CA'
AND DATE IN ('01/28/2000','02/25/2000','03/31/2000')
GROUP BY V.SNO
Is this syntax not supported yet?
I don't know this subselect syntax; which db are you using?