时间戳

首页 > 解决方案 > 为什么存储过程为每个组返回错误的总和？

sql - 为什么存储过程为每个组返回错误的总和？

问题描述

我有这个查询，但SUM(SUM(inv.ServicePrice)) over () as TotalRevenueAllServices 返回错误的总和。我实际上想得到我为每个组所做的所有总和，但它返回错误的值。

Select top(3) s.ServiceName, Count(inv.fk_ServiceID) as TotalServicesCount, Sum(inv.ServicePrice) TotalServicesRevenue,
        SUM(SUM(inv.ServicePrice)) over () as TotalRevenueAllServices
        from Invoices inv
        inner join Services s
        on s.ServiceID= inv.fk_ServiceID
        group by inv.fk_ServiceID, s.ServiceName
        Order By ServiceName asc

标签： sqlsql-servertsqlstored-procedures

解决方案

declare @FromDate date= '22/Nov/2017',
        @ToDate date= '24/Nov/2018'


        Set @ToDate= ISNULL(@ToDate, getdate());


        with MainTable
        as
        (
        Select top(3) s.ServiceName, Count(inv.fk_ServiceID) as TotalServicesCount, Sum(inv.ServicePrice) TotalServicesRevenue      
        from Invoices inv
        inner join Services s
        on s.ServiceID= inv.fk_ServiceID
        --where CAST(inv.EntryDateTime as DATE) between @FromDate and @ToDate
        group by inv.fk_ServiceID, s.ServiceName
        Order By ServiceName asc
        )
        select * , (select sum(totalservicesrevenue) from MainTable) 'AllServicesRevenue'
        ,          (select sum(TotalServicesCount) from MainTable) 'OverallServices'
                    from MainTable

推荐阅读