标签归档:sum

mssql sqlserver sum显示多列的方法分享


摘要:
下文讲述sqlserver sql脚本中,同时对多列进行sum计算,如下所示:
实验环境:sql server 2008 R2


在sqlserver中,当我们需要sum显示多列时,此时我们可以采取同时对表的不同列执行sum操作,如下所示:

    create table [maomao365]
(keyId int identity,
qty1 int,
qty2 int,
qty3 int)
go

/*生成数据*/
insert into [maomao365]
(qty1,qty2,qty3)
values
(8,9,10),
(1,2,3),
(5,4,null),
(6,11,13),
(7,12,14)
go


/*多列分别sum求和*/

select sum(qty1) as [qty1],sum(qty2) as [qty2],
sum(qty3) as [qty3] from [maomao365]


/*sum同时多列一起求和*/
select sum(isnull(qty1,0)+isnull(qty2,0)+isnull(qty3,0)) as [qty] 
 from [maomao365]


select sum(qty1+qty2+qty3) as [qty] 
 from [maomao365]

go
truncate table [maomao365]


sum求和注意事项:
当使用sum聚合函数进行求和运算时,系统会过滤掉列值为null的行

mssql sqlserver sum函数计算后的结果中包含null的处理方法分享


摘要:
在mssql进行聚合计算时,有时候查询出来的结果包含null,此时我们可采用以下方法将null转换为0



方法1:使用isnull替换

    

 create table #maomao365 
(qty int)
go


insert into #maomao365(qty)
values(1),(2),(3),(null),(5),(6),
(null)
go


select isnull(sum(qty),0) from #maomao365
where qty is null 




go
truncate table #maomao365 
drop table #maomao365


方法2:使用case when 替换

    

 create table #maomao365 
(qty int)
go


insert into #maomao365(qty)
values(1),(2),(3),(null),(5),(6),
(null)
go


select case 
         when sum(qty) is null then 0
          else   sum(qty) 
          end 
           from #maomao365
          where qty is null 




go
truncate table #maomao365 
drop table #maomao365


方法3:用coalesce替换相应的值

    

 create table #maomao365 
(qty int)
go


insert into #maomao365(qty)
values(1),(2),(3),(null),(5),(6),
(null)
go


select coalesce(sum(qty),0) from #maomao365
where qty is null 




go
truncate table #maomao365 
drop table #maomao365

mssql sqlserver sum求和函数简介(用法说明)


摘要:
下文讲述mssql sqlserver中sum函数用法说明,如下所示:
实验环境:sql server 2008 R2



sum函数语法简介
SELECT SUM(表达式)
FROM 表名
WHERE [筛选条件];
—参数说明——-
表达式:可以为一个字段也可以为一个特定的数值

例1:

     ----获取9月薪水大于13000的员工的总薪水支出
    select sum(xinShui)  as [总薪水支出]
     from [支出表]
     where [薪水] > 13000
               and  [支出月份] =9
  

例2:
sum同distinct一起使用,统计单一类型的合计数

     select sum(distinct xinShui)  as [总薪水支出]
        from [支出表]
          where [薪水] > 13000
              and  [支出月份] =9
  

例3:
sum统计两列之间的计算后求和

 
     select sum(A-B) from 
      tableName 
     where []
   

例4:
sum统计“单列计算后”求和

    select sum(A*0.88) from 
      tableName 
     where []
   

例5:
sum同group by 一起使用

    select userName,sum(xinShui) as [薪水] 
     from tableName 
     where  [支出月份] =9 
     group by userName