CASE语句和group by

Question

select  [Major Genre],count(*) as 'Movie Count', 
        sum([US Gross]) as 'Total US Gross',
        sum([Worldwide Gross]-[Production Budget]) as 'Profit', 
        (sum([Worldwide Gross]-[Production Budget])/SUM([Production Budget]))*100 as 'Gross Profit',
    case when sum([US Gross])>=200000000 AND sum([US Gross])<=999000000 
         then 'US Gross more then 220M'
         when sum([US Gross])>=999000000 
         then 'US Gross more then 999M'
     end AS 'US Gross Status'
from Sheet1$
group by [Major Genre],[US Gross]
having [US Gross] > 220000000
order by [Major Genre]

我希望它只显示关于每个主要流派的汇总信息，现在我在每个主要流派上得到了几行，我如何解决这个问题？

Answer 1

如果您可以提供示例数据和所需的输出，这将有所帮助，但是查看您的查询，我可以看到它需要稍微调整的地方。您需要从Group by中删除[US Gross]，并在Having子句中对其执行sum操作。通过按它分组，您将获得每个不同值的行，尽管使用了sum。

select [Major Genre], count(*) as 'Movie Count', 
sum([US Gross]) as 'Total US Gross',
sum([Worldwide Gross]-[Production Budget]) as 'Profit', 
(sum([Worldwide Gross]-[Production Budget])/SUM([Production Budget]))*100 as 'Gross Profit',
    case when sum([US Gross])>=200000000 AND sum([US Gross])<=999000000 
    then 'US Gross more then 220M'
    when sum([US Gross])>=999000000 
    then 'US Gross more then 999M'
    end AS 'US Gross Status'
from Sheet1$
group by [Major Genre]
having Sum([US Gross]) > 220000000
order by [Major Genre]