SELECT
  T.title_id,
  S.stor_id,
  ST.stor_name,
  city,
  state,
  Revenue = T.price * S.qty
FROM
    titles AS T
  JOIN
    sales  AS S   ON T.title_id = S.title_id
  JOIN
    stores AS ST  ON S.stor_id  = ST.stor_id 
  JOIN
    (SELECT T2.title_id, .80 * AVG(price * qty) AS avg_val
     FROM titles AS T2 JOIN sales AS S2 
       ON T2.title_id = S2.title_id
     GROUP BY T2.title_id) AS AV ON  T.title_id = AV.title_id
                                 AND T.price * S.qty < avg_val
Published in: SQL
Download

Related snippets