sql - 如何根据where条件对行求和

Question

我将数据集以以下格式存储在 excel 中。

我写的查询结果低于结果。

查询应返回一行。如果我输入额外的计算列，也会发生同样的情况。因此，如果我同样添加 amount2、amount3，行数增加 4（每个金额 2），总计 6 行，其中 3 行将返回 NULL 值。

表结构、示例数据和我的查询如下：

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3f8c9931554863583616d0f662230996

标签： sqlsql-server

Answer 1

你可以使用过滤器

having sum(sales) is not null

在你的小提琴之后编辑

select CustomerName,BillNo,  sum(Amount)
from [dbo].[MarginTemp]
where [Type] = 'A'
group by  customername,BillNo

在编辑你今天的

SELECT  
       MAT.BillNo,MAT.ServiceCategoryName, 
       'Sep-19' AS MonthName,
    SUM(case when MAT.ServiceCategoryName = 'Z-Net Revenue'
     then MAT.Amount else 0 end) AS Amount

FROM MarginTemp MAT

WHERE 
MAT.BillNo='A202019339291'
GROUP BY MAT.BillNo,
         MAT.ServiceCategoryName
         having SUM(case when MAT.ServiceCategoryName = 'Z-Net Revenue'
     then MAT.Amount else 0 end)>0
ORDER BY BillNo ASC;

您在查询中得到 null 是因为您 MAT.BillNo,MAT.ServiceCategoryName在 group by 中使用了只有一个 ServiceCategoryName 满足条件，而对于其他人它显示为 null

经过讨论，我得到了原来的问题

with cte as
(SELECT MAT.BillNo,
       ---MAT.ServiceCategoryName, 
       'Sep-19' AS MonthName
       ,  SUM( case when MAT.ServiceCategoryName='Z-NET Revenue' then MAT.Amount else 0 end) as AMOUNT
      , SUM( case when MAT.ServiceCategoryName='Internal Dr Visit Charges' then MAT.Amount else 0 end) as [Internal Dr Visit Charges]
      ,SUM( case when MAT.ServiceCategoryName='External Dr Visit Charges' then MAT.Amount else 0 end) as [External Dr Visit Charges]

FROM MarginTemp MAT
WHERE MAT.BillNo = 'A202019339291'

GROUP BY MAT.BillNo, 
         MAT.ServiceCategoryName
) select BillNo,MonthName,max(AMOUNT) as amt,
max([Internal Dr Visit Charges]) as [Internal Dr Visit Charges]
,max([External Dr Visit Charges]) as [External Dr Visit Charges]
from cte
group by BillNo,MonthName