sql - 在同一查询中使用 count(*) 结果字段

Question

您好，我正在使用以下查询来计算每项 Assets 的平均漏洞数量趋势。我需要使用 count(*) 操作的结果进行进一步计算，如您在我的查询中所见。不幸的是，我有错误，列 scanned_asset 不存在。我该如何解决？

SELECT to_char(fad.day, 'DD-MM-YYYY') AS day, count(*) AS scanned_assets,
scanned_assets/(SELECT DISTINCT vulnerabilities FROM fact_all_date fadt WHERE 
fadt.day=fad.day) AS avg,
FROM fact_asset_date fad
INNER JOIN dim_asset da USING(asset_id)
WHERE (da.credential_status='All credentials successful' OR 
da.credential_status='Credentials partially successful')
GROUP BY fad.day
ORDER BY fad.day ASC

Answer 1

这样的东西应该有用吗？

SELECT 
    x.day, 
    x.scanned_assets, 
    x.scanned_assets / (SELECT DISTINCT vulnerabilities FROM fact_all_date fadt WHERE fadt.day = x.day) AS avg
FROM
(SELECT 
    TO_CHAR(fad.day, 'DD-MM-YYYY') AS day, 
    COUNT(*) AS scanned_assets
FROM 
    fact_asset_date fad
    INNER JOIN dim_asset da USING (asset_id)
WHERE 
    da.credential_status = 'All credentials successful' 
    OR da.credential_status = 'Credentials partially successful'
GROUP BY 
    fad.day) x
ORDER BY 
    x.day;

编辑:删除方括号，因为 POSTGRESQL 不喜欢它们。