我想要一个查询,只显示高于平均水平的合同成本以及合同 id,我用这个创建我的平均值:
SELECT AVG(average_cost)
FROM (
SELECT SUM(contracts.hours*staff.rate) AS average_cost
FROM contracts
INNER JOIN staff ON contracts.staff_id = staff.staff_id
GROUP BY contracts.contract_id
) AS inner;
我希望结果显示contract_id并按它分组,即使它显示的只是高于平均水平的结果contract_id。只是很难让查询显示高于平均水平的结果,这个平均值比我习惯的要复杂得多。感谢帮助,谢谢。
使用一些示例数据编辑*,表格比这更复杂,但我希望这有助于一些理解
contracts
contract_id hours staff_id
55 30 10
45 25 11
43 30 12
41 12 11
67 20 12
49 20 13
staff
staff_id rate
10 50
11 45
12 80
13 45
因此,上述平均合同成本约为1,344.167。对于此示例,我希望结果显示成本高于平均水平的合同的contract_id。
results
contract_id
55
43
67
这里有一种方法:
SELECT c.contract_id, SUM(c.hours*s.rate) AS cost
FROM contracts c INNER JOIN
staff s
ON c.staff_id = s.staff_id
GROUP BY c.contract_id
HAVING cost > (SELECT AVG(average_cost)
FROM (SELECT SUM(c.hours*s.rate) AS average_cost
FROM contracts c INNER JOIN
staff s
ON c.staff_id = s.staff_id
GROUP BY c.contract_id
) sc
);
我对你的问题很了解,我认为你正在寻找这个:
select contract_id
from contracts join staff on (contracts.staff_id = staff.staff_id)
where (hours*rate) >
(select avg(hours*rate)
from contracts join staff on (contracts.staff_id = staff.staff_id)
)
我有我的例子可以帮助你:
SELECT first_name, last_name, invoice_amount
FROM students
INNER JOIN invoices
ON students.id_student = invoices.id_student
WHERE invoice_amount > (select avg(invoice_amount) from invoices)