SQL getting averages with multiple joins

Question

I'm trying to write a single query using 3 tables.
The tables and their columns that I will be using are:

Sec – ID, Symbol
Hss – Code, HDate, Holiday
Fddd – ID, Date, Price

Given a symbol AAA, I need to get the ID from the first table and match it with the ID from the third table. The second table's date must match the third table's dates with the condition of Code=1 and Holiday=1.

The Dates in the second and third table are in ascending order with most recent dates at the bottom. I want to get the average 50 day and 200 day prices. The dates in the tables are in ascending order so I want to make it descending and select the top 50 and 200 to get the average prices.

So far I can only get one average. I cannot add a second SELECT TOP 50 or add a subquery within the second avg().

SELECT AVG(TwoHun)TwoHunAvg   --, AVG(Fifty) AS FiftyAvg 
FROM (SELECT TOP 200 Fddd.price AS TwoHun    --, TOP 50 Fddd.price AS Fifty
FROM Sec 
JOIN Fddd
ON Sec.ID = Fddd.ID AND Sec.symbol = 'AAA' 
JOIN Hss 
ON Fddd.date = Hss.Hdate AND Hss.Code = 1 AND Hss.Holiday = 1 
ORDER BY Fddd.Date DESC) AS tmp;

Thanks in advance!

It would also help to have sample data in order to be able to test! Also your question is a bit confusing, please provide 2 tables. One with data, and one with what you want as the results. — Menelaos, Jul 31 '15 at 19:35
@mustaccio I think it's ms access or SQL server. Ref: http://www.w3schools.com/sql/sql_top.asp — Menelaos, Jul 31 '15 at 19:39

score 0 · Answer 1 · edited Jan 17 '16 at 13:54

0

I suspect your using SQL Server or MS Access.

One quick solution would be to have your total query as a subquery and then copy a modified version as a second subquery.

Quick rough example:

    SELECT (SELECT
         AVG(Fifty) AS FiftyAvg
       FROM (SELECT TOP 50
         Fddd.price AS Fifty
       FROM Sec
       JOIN Fddd
         ON Sec.ID = Fddd.ID
         AND Sec.symbol = 'AAA'
       JOIN Hss
         ON Fddd.date = Hss.Hdate
         AND Hss.Code = 1
         AND Hss.Holiday = 1
       ORDER BY Fddd.Date DESC) AS tmp)
       AS FiftyAvg,
       (SELECT
         AVG(TwoHun) TwoHunAvg
       FROM (SELECT TOP 200
         Fddd.price AS TwoHun
       FROM Sec
       JOIN Fddd
         ON Sec.ID = Fddd.ID
         AND Sec.symbol = 'AAA'
       JOIN Hss
         ON Fddd.date = Hss.Hdate
         AND Hss.Code = 1
         AND Hss.Holiday = 1
       ORDER BY Fddd.Date DESC) AS tmp)
       AS TwoHundredAverge;

edited Jan 17 '16 at 13:54

marc_s

732,580
175
1,330
1,459

answered Jul 31 '15 at 19:46

Menelaos

23,508
18
90
155

Am I correct that this query assumes `Dual` is a saved table in database of one record? – Parfait Jul 31 '15 at 20:55
actually dual is a dummy table keyword. The actual queries are the subqueries – Menelaos Jul 31 '15 at 20:57
Actually no dual table reference is needed, reference: http://stackoverflow.com/a/28371433/1688441 – Menelaos Jul 31 '15 at 21:03

score 0 · Answer 2 · answered Jul 31 '15 at 20:53

Consider a union query which even scales for other Averages. I add a Type column to indicate the Averages.

SELECT '200 DAY AVG' As Type, AVG(TwoHun) As Avg
FROM 
  (SELECT TOP 200 Fddd.price AS TwoHun
   FROM Sec 
   INNER JOIN Fddd ON Sec.ID = Fddd.ID 
   INNER JOIN Hss ON Fddd.date = Hss.Hdate
   WHERE Sec.symbol = 'AAA' AND Hss.Code = 1 AND Hss.Holiday = 1 
   ORDER BY Fddd.Date DESC) AS tmp;

UNION

SELECT '50 DAY AVG' As Type, AVG(FiftyHun) As Avg
FROM 
  (SELECT TOP 50 Fddd.price AS FiftyHun
   FROM Sec 
   INNER JOIN Fddd ON Sec.ID = Fddd.ID
   INNER JOIN Hss ON Fddd.date = Hss.Hdate
   WHERE Sec.symbol = 'AAA' AND Hss.Code = 1 AND Hss.Holiday = 1 
   ORDER BY Fddd.Date DESC) AS tmp;

Also, I moved some of your join expressions to where clause which should not change performance but does in readability.

This isn't exactly what the user asked for since you are providing the values in two different rows when instead they were requested as two different columns. — Menelaos, Aug 01 '15 at 18:14
Correct, hence the "consider". You beat me to the original request. I provide an alternative to OP and maybe future readers. And 100-day, 30-day, 15-day can be added for an Avgs dataset. Plus, there's the performance aspect of quadruple vs double subqueries... — Parfait, Aug 02 '15 at 02:19

SQL getting averages with multiple joins

2 Answers2