时间戳

首页 > 解决方案 > 当我的价格在 Apex Oracle SQL 的不同表中时，如何找到分组范围的平均价格？

sql - 当我的价格在 Apex Oracle SQL 的不同表中时，如何找到分组范围的平均价格？

问题描述

SCORE_T 表的图片 我正在尝试找到一种方法将我的平均价格编码到我从单独的表中建立的范围内。因此，我用来形成范围的点来自我的 POINTS_T 表，并且我使用出现次数通过使用 COUNT(*) 来建立一列。我现在试图找到每个范围的平均价格，但我的价格在 WINE 表中，我遇到了麻烦，因为我使用的是子查询并且不知道在哪里包含它。我附上了两张图片，其中一张是我没有错误的原始代码（因为我没有尝试包含平均价格），第二张包括我尝试将其包含在子查询中。

这是我到目前为止的代码（不包括我的平均价格尝试）。

SELECT t.range as "Score Range", COUNT(*) as "Number of Occurrences" 
FROM (
  SELECT Case  
    when SCORE_T.Points between 95 and 100 then '95-100'
    when SCORE_T.Points between 90 and 94 then '90-94'
    when SCORE_T.Points between 85 and 89 then '85-89'
    when SCORE_T.Points between 80 and 84 then '80-84'
    end as RANGE
  FROM SCORE_T) t
GROUP BY t.range
ORDER BY COUNT(*) DESC
;

这就是我的尝试，这会产生错误。

SELECT t.range as "Score Range", COUNT(*) as "Number of Occurrences"
FROM (
  SELECT Case  
    when SCORE_T.Points between 95 and 100 then '95-100'
    when SCORE_T.Points between 90 and 94 then '90-94'
    when SCORE_T.Points between 85 and 89 then '85-89'
    when SCORE_T.Points between 80 and 84 then '80-84'
    end as RANGE, avg(WINE.Price) as "Average Price of Wine"
  FROM SCORE_T, WINE) t
GROUP BY t.range
ORDER BY COUNT(*) DESC
;

谢谢！

标签： sqloracle-apex

解决方案

您需要 aJOIN并且您正在使用avg()no GROUP BY。我会期待这样的事情：

SELECT sw.range as Score_Range,
       COUNT(*) as Number_of_Occurrences,
       AVG(sw.Price) as Average_Price
FROM (SELECT (Case when s.Points between 95 and 100 then '95-100'
                   when s.Points between 90 and 94 then '90-94'
                   when s.Points between 85 and 89 then '85-89'
                   when s.Points between 80 and 84 then '80-84'
              end) as RANGE,
           w.Price as Price
     FROM SCORE_T s JOIN
          WINE w
          ON s.? = w.?  -- what are the JOIN keys???
    ) sw
GROUP BY sw.range
ORDER BY COUNT(*) DESC;

?是连接乐谱和葡萄酒表的列的占位符。

推荐阅读