标签归档:聚合函数

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的行

SQLServer用COMPUTE 和 COMPUTE BY(汇总) 关键字举例应用


摘要:
下文讲述 compute 及 compute by 关键字的用法简介



一、compute compute by 关键字用法说明

compute 和 compute by 关键字的功能:
返回群组所使用的明细数据和聚合结果,可非常方便看出聚合结果的数据来源
compute by 前面必须使用order by ,sql引擎将根据order by 后的列名排序,compute by后面的列名进行聚合计算,依次返回聚合明细和聚合值



compute和compute by 举例说明

 create table [maomao365.com]
(sort nvarchar(30),qty  int,
 subSort varchar(30)    )
go
insert into [maomao365.com]
          (sort, qty,subSort)   values
          ('A类',10,'香水类'),('B类',30,'皮革类'),
          ('A类',88,'香水类'),('C类',56,'其它类'),
          ('D类',9,'其它类'),('A类',10,'饰品类')
go
---compute 关键字 生成 qty 之和和行数
select sort,subsort,qty from 
   [maomao365.com]
   compute sum(qty),count(qty)
---compute by 根据sort subsort
select sort,subsort,qty from 
   [maomao365.com] 
   order by sort,subsort  
   compute sum(qty)  by sort ,subsort 

---compute by 根据sort群组生成 qty之和行数
select sort,subsort,qty from 
   [maomao365.com] 
   order by sort,subsort  
   compute sum(qty), count(subSort)  by sort  


go
truncate table   [maomao365.com]
drop     table   [maomao365.com]

mssql sqlserver sum(1)、sum(2)、count(1)、count(8)、count(*)、统计函数应用区别简介


摘要:
下文着重讲述sum同count统计函数的区别,如下所示:
实验环境:sqlserver 2008 R2



一、sum count函数简介

sql统计函数中:
sum:对指定字段的表达式值进行求和计算
count:对指定表达式的行数进行计算

sum(1)等同于count函数,但是count函数效率更高



二、sum count函数举例说明
create table test(A int,B int,info varchar(10))
go

insert into test(A,B,info)values(5,6,’A’),
(2,4,’B’),(8,9,’B’),(3,1,’A’),(7,3,’C’),(1,2,null)
go

select info,sum(1) as [sum(1)],
count(*) as [count(*)],
count(6) as [count(6)],
count(8) as [count(8)],
count(info) as [count列],
sum(2) as [sum(2)],
sum(A) as [sum列值]
from test
GROUP BY info
/*
从以上的sql脚本中,可以看出
sum(1)同count(*) count(6) count(8) count(info)都是统计行数,返回结果一致
—sum(2)对每一行采用值2相加
count(6) count(8) 不会由于输入的值不同而得到不同的值
—count(列) 会过滤掉列值等于null的行
及count(列名);只统计列值中不会null的行数
*/
go
truncate table test
drop table test