How should I do this query using Oracle SQL ?

From: Adrian Hodson <AdrianHodson_at_Compuserve.com>
Date: Thu, 10 Sep 1998 17:31:23 -0400
Message-ID: <01bddd02$8d0f38a0$0f81588e_at_wbra0086>

I have two tables where in each case the first set of columns are the primary
keys and the second sets are numbers.

Purchases

	Year
	Week
	Location
	ProductGroup
	ProductDept
	Brand

	Sales
	Baskets

Visits
	Year
	Week
	Location

	Visitors

With Microsoft Access I can write a query like this which will sum amounts from the two
tables independently and then perform some calculations with them.

SELECT Pu.Year, Pu.Week, Sum(Pu.Sales) AS TotalSales, Sum(Pu.Baskets) AS TotalBaskets,
(SELECT Sum(Visitors) from Visitors Vi
 WHERE Pu.Year = Vi.Year
   and Pu.Week = Vi.Week
 GROUP BY Vi.Year, Vi.Week ) AS Visitors2, Sum(Pu.Sales) / Visitors2 AS PerVisitor, 100 * Sum(Pu.Baskets) / Visitors2 AS Penetration
FROM Purchases AS Pu
GROUP BY Pu.Year, Pu.Week;

How should I do this with Oracle 7 ?

Thanks
Adrian Hodson Received on Thu Sep 10 1998 - 23:31:23 CEST