时间戳

首页 > 解决方案 > AVG() 正在复制存储在行中的数据，而不是提供列的平均数

sql - AVG() 正在复制存储在行中的数据，而不是提供列的平均数

问题描述

我创建了一个查询来从多个列中检索数据。但是，当使用 AVG(*) AVERAGE 显示给定列的值时，该函数只是复制并显示每一行中的数据。我本质上希望表格显示查询中的每一列，并在每一行中显示表格平均值。

如果我在选择语句中没有多个列，那么我可以查询表以显示给定列的真实平均值就好了。我尝试使用 UNIONs 和 SELF JOINs 来查询 AVG(*) AVERAGE，但没有运气。

CREATE TABLE INVOICE
(
    INV_NUM NUMBER NOT NULL PRIMARY KEY,
    CUST_NUM NUMBER NOT NULL REFERENCES CUSTOMER(CUST_NUM),
    INV_DATE DATE NOT NULL,
    INV_AMOUNT NUMBER NOT NULL
);


INSERT ALL
INTO (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) 
VALUES ('8000','1000','3/23/2014','235.89')


INTO (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) 
VALUES ('8001','1001','3/23/2014','312.82')


INTO (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) 
VALUES ('8002','1002','3/30/2014','528.10')       


INTO (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) 
VALUES ('8003','1003','4/12/2014','194.78')


INTO (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) 
VALUES ('8004','1004','4/23/2014','619.44')

SELECT * FROM DUAL;

--------------TABLE QUERIES----------------

SELECT INV_NUM, INV_AMOUNT, AVG(INV_AMOUNT) AVERAGE, 
AVG(INV_AMOUNT)-INV_AMOUNT DIFFERENCE
FROM INVOICE
GROUP BY INV_NUM, INV_AMOUNT;

标签： sqloracleoracle11gaggregate-functions

解决方案

像这样的东西：

with invoice as (
select 
8000 INV_NUM,1000 CUST_NUM,'3/23/2014' INV_DATE,235.89 INV_AMOUNT
from dual union all
select 8001,1001,'3/23/2014',312.82
from dual union all
select 8002,1002,'3/30/2014',528.10
from dual union all
select 8003,1003,'4/12/2014',194.78
from dual union all
select 8004,1004,'4/23/2014',619.44 from dual)
SELECT INV_NUM, INV_AMOUNT, AVERAGE, 
AVERAGE-INV_AMOUNT DIFFERENCE
FROM INVOICE,(select avg(inv_amount) average from invoice)
ORDER BY INV_NUM, INV_AMOUNT

推荐阅读