使用临时表时，SQL COUNT查询返回NULL

Question

我使用以下代码尝试根据专辑中的歌曲数量返回平均收入。

WITH Album_Tracks AS (
SELECT COUNT(*) AS "#ofTracks", SUM(invoice_items.UnitPrice) AS "Album_Revenue"
from tracks
LEFT JOIN invoice_items ON invoice_items.trackID = tracks.trackID
GROUP BY Albumid
)
SELECT #ofTracks, AVG(Album_Revenue)
FROM Album_Tracks
GROUP BY #ofTracks
ORDER BY #ofTracks DESC

我遇到的问题是轨道的"；当我没有从表中选择所有数据时，临时表"Album_Tracks"中的列返回一列NULL值。

与中一样，这样做：SELECT * FROM Album_Tracks返回值，但SELECT #ofTracks FROM Album_Tracks只返回null值。

我不确定我的代码有什么问题。

期望看到：示例1

但我得到了：示例2

第一个示例不包括订单。

Answer 1

只是一个建议
尽量避免不允许的字符(在表和列名中(作为+ - ? ! * @ % ^ & # = / : " '

WITH Album_Tracks AS (
SELECT COUNT(*) AS NumOfTracks
, SUM(invoice_items.UnitPrice) AS Album_Revenue 
from tracks
LEFT JOIN invoice_items ON invoice_items.trackID = tracks.trackID
GROUP BY Albumid
)
SELECT NumOfTracks, AVG(Album_Revenue) F
ROM Album_Tracks
GROUP BY NumOfTracks
ORDER BY NumOfTracks DESC

Answer 2

尝试一个简单的：

SELECT #ofTracks

它将返回NULL
将#ofTracks用双引号、倒勾号或方括号括起来：

SELECT "#ofTracks", AVG(Album_Revenue) FROM Album_Tracks
GROUP BY "#ofTracks"
ORDER BY "#ofTracks" DESC