1. sql 报表统计 总计小计
select 月份,电费,管理费,杂费,餐费,其他,总计 from
(select cast((month(DATE)) as char(2)) as 月份一,cast((month(DATE))as char(2)) as 月份,
sum(case when OUTPUT_STYTLE='电费' then AMOUNT else 0 end) as 电费,
sum(case when OUTPUT_STYTLE='管理费' then AMOUNT else 0 end) as 管理费,
sum(case when OUTPUT_STYTLE='杂费' then AMOUNT else 0 end) as 杂费,
sum(case when OUTPUT_STYTLE='餐费' then AMOUNT else 0 end) as 餐费,
sum(case when OUTPUT_STYTLE='其他' then AMOUNT else 0 end) as 其他,
sum(case when ID>0 then AMOUNT else 0 end ) as 总计
from DB_DAILY_OUTPUT where(year(DATE)='"+year+"') group by (month(DATE))
union
select '13' as 月份一,'合计' as 月份,
sum(case when OUTPUT_STYTLE='电费' then AMOUNT else 0 end) as 电费,
sum(case when OUTPUT_STYTLE='管理费' then AMOUNT else 0 end) as 管理费,
sum(case when OUTPUT_STYTLE='杂费' then AMOUNT else 0 end) as 杂费,
sum(case when OUTPUT_STYTLE='餐费' then AMOUNT else 0 end) as 餐费,
sum(case when OUTPUT_STYTLE='其他' then AMOUNT else 0 end) as 其他,
sum(case when ID>0 then AMOUNT else 0 end ) as 总计
from DB_DAILY_OUTPUT where(year(DATE)='"+year+"') )a
order by 月份一
2. sql怎么讲查询结果求和如图
你好，很高兴回答你的问题。最简单的就是把你的这个查询当做一个子查询来求和。
select sum(总计) from (你的查询)
如果有帮助到你，请点击采纳。
3. sql 中怎么把两个总计结果都显示在一个字段里
有个 lpad 的函数可以左边补0
4. 如何使用sql语句查询出特殊的汇总结果
SELECT DISTINCTROW 员工,消费项目, Sum(数量) AS 总计
FROM zj_detial
GROUP BY 员工, 消费项目
ORDER BY 员工, 消费项目;
****************************************************
那个 第一个列的相同数据就不显示了 我做不到
5. SQL字段累加求和的问题
SELECT ID,STRING1,STRING2 from table UNION
select ID=0,string1=string1+'小计',countresult=count(1),sum1=sum(string2) from talbe group by string1 union
select ID=0,string1='总计',countresult=count(1),sum1=sum(string2) from table order by string1,id
结果为包括了小计和总计,和明细,并按顺序排列
6. sql如何实现总计
算出总计行，使用unionall并起来就可以。但是这样会算多一遍，建议计算总数还是放在后台代码或者前端进行计算，这样才比较节省性能。
SELECT
a.BUFullName'公司',
a.ProjName'项目',
a.BldName'楼栋',
a.count_num'总套数',
a.total'总按揭金额',
b.count_num'未完成套数',
b.total'未办理金额'
FROM
(
SELECT
p2.BUFullName,
p1.ProjName,
p6.BldName,
count(p6.BldName)'count_num',
sum(p3.AjTotal)'total'
FROM
p_ProjectASp1
.BUGUID=p1.BUGUID
LEFTJOINs_SaleServiceASp4ONp4.ProjGUID=p1.p_Projectid
ANDp4.ServiceItemEnum='2'
RIGHTJOINs_ContractASp3ONp3.ContractGUID=p4.SaleGUID
ANDp3.StatusEnum='1'
INNERJOINs_RoomASp5ONp5.RoomGUID=p3.RoomGUID
INNERJOINs_BuildingASp6ONp6.BldGUID=p5.BldGUID
ANDp6.IsBld!='0'
WHERE
p1.p_Projectid=@projGUID
GROUPBY
p2.BUFullName,
p1.ProjName,
p6.BldName
)ASaleft
JOIN(
SELECT
j6.BldName,
count(j6.BldName)'count_num',
sum(j3.AjTotal)'total'
FROM
p_ProjectASj1
.BUGUID=j1.BUGUID
LEFTJOINs_SaleServiceASj4ONj4.ProjGUID=j1.p_Projectid
ANDj4.ServiceItemEnum='2'
ANDj4.ServiceProcCode='1'
RIGHTJOINs_ContractASj3ONj3.ContractGUID=j4.SaleGUID
ANDj3.StatusEnum='1'
INNERJOINs_RoomASj5ONj5.RoomGUID=j3.RoomGUID
INNERJOINs_BuildingASj6ONj6.BldGUID=j5.BldGUID
ANDj6.IsBld!='0'
WHERE
j1.p_Projectid=@projGUID
GROUPBY
j6.BldName,
j2.BUFullName,
j1.ProjName
)ASbONa.BldName=b.BldNameORDERBYa.BldName
unionALL
SELECT
'总计''公司',
'总计''项目',
'总计''楼栋',
sum(a.count_num)'总套数',
sum(a.total)'总按揭金额',
sum(b.count_num)'未完成套数',
sum(b.total)'未办理金额'
FROM
(
SELECT
p2.BUFullName,
p1.ProjName,
p6.BldName,
count(p6.BldName)'count_num',
sum(p3.AjTotal)'total'
FROM
p_ProjectASp1
.BUGUID=p1.BUGUID
LEFTJOINs_SaleServiceASp4ONp4.ProjGUID=p1.p_Projectid
ANDp4.ServiceItemEnum='2'
RIGHTJOINs_ContractASp3ONp3.ContractGUID=p4.SaleGUID
ANDp3.StatusEnum='1'
INNERJOINs_RoomASp5ONp5.RoomGUID=p3.RoomGUID
INNERJOINs_BuildingASp6ONp6.BldGUID=p5.BldGUID
ANDp6.IsBld!='0'
WHERE
p1.p_Projectid=@projGUID
GROUPBY
p2.BUFullName,
p1.ProjName,
p6.BldName
)ASaleft
JOIN(
SELECT
j6.BldName,
count(j6.BldName)'count_num',
sum(j3.AjTotal)'total'
FROM
p_ProjectASj1
.BUGUID=j1.BUGUID
LEFTJOINs_SaleServiceASj4ONj4.ProjGUID=j1.p_Projectid
ANDj4.ServiceItemEnum='2'
ANDj4.ServiceProcCode='1'
RIGHTJOINs_ContractASj3ONj3.ContractGUID=j4.SaleGUID
ANDj3.StatusEnum='1'
INNERJOINs_RoomASj5ONj5.RoomGUID=j3.RoomGUID
INNERJOINs_BuildingASj6ONj6.BldGUID=j5.BldGUID
ANDj6.IsBld!='0'
WHERE
j1.p_Projectid=@projGUID
GROUPBY
j6.BldName,
j2.BUFullName,
j1.ProjName
)ASbONa.BldName=b.BldName
7. 用sql 如何实现 报表的分组的小计，合计！最后还有一个总计
如果要使用SQL来实现的话，可以采用临时表，但是这样必然会牺牲效率。
SELECT * INTO #TEMP FROM 表
INSERT INTO #TEMP VALUES(....) --这一句是你的小计、合计、总计
SELECT * FROM #TEMP
过程基本如此，需要注意的是临时表字段的类型，如果出现某字段不允许为空的话，可以采用如下语句来变更字段类型：
ALTER TABLE #TEMP
ALTER COLUMN 字段名 类型 NULL
最后要注意的是排序，要使得小计、合计、总计在结果集的最后。
8. 数据库中如何分类、分组并总计SQL数据
需要用group
by语句来统计。
1、创建测试表、插入数据：
createtabletest
(idint,
namevarchar(10),
scoreint,
classnamevarchar(20));
insertintotestvalues(1,'张三',100,'一班');
insertintotestvalues(2,'李四',89,'一班');
insertintotestvalues(3,'王五',97,'一班');
insertintotestvalues(4,'赵六',87,'二班');
insertintotestvalues(5,'孙七',94,'二班');
insertintotestvalues(6,'杨八',76,'二班');
2、查询每个班级的总分，可用如下语句：
selectclassname,SUM(score)as总分fromtestgroupbyclassname;
3、结果截图：
9. 如何得到SQL查询总和
SELECT SUM(A.COL) FROM A;--求具体那一列的和值
SELECT COUNT(1) FROM A;--求查询结果总数
10. 如何用SQL语句查询出业务员每个月的业绩和总计
IFOBJECT_ID('saleinfo')ISNOTNULLDROPTABLEsaleinfo
--创建模拟表
createtablesaleinfo(
idintidentity(1,1)primarykey,
salesmannvarchar(50)null,
salemonthnvarchar(50)null,
achievementint
)
--载入模拟数据
insertintosaleinfo(salesman,salemonth,achievement)values('A','1月5日',10)
insertintosaleinfo(salesman,salemonth,achievement)values('A','1月7日',20)
insertintosaleinfo(salesman,salemonth,achievement)values('A','2月4日',30)
insertintosaleinfo(salesman,salemonth,achievement)values('A','2月7日',40)
insertintosaleinfo(salesman,salemonth,achievement)values('A','3月2日',50)
insertintosaleinfo(salesman,salemonth,achievement)values('A','3月9日',60)
insertintosaleinfo(salesman,salemonth,achievement)values('B','1月5日',70)
insertintosaleinfo(salesman,salemonth,achievement)values('B','1月7日',80)
insertintosaleinfo(salesman,salemonth,achievement)values('B','2月4日',90)
insertintosaleinfo(salesman,salemonth,achievement)values('B','2月7日',10)
insertintosaleinfo(salesman,salemonth,achievement)values('B','3月2日',20)
insertintosaleinfo(salesman,salemonth,achievement)values('B','3月9日',30)
insertintosaleinfo(salesman,salemonth,achievement)values('C','1月5日',40)
insertintosaleinfo(salesman,salemonth,achievement)values('C','1月7日',50)
insertintosaleinfo(salesman,salemonth,achievement)values('C','2月4日',60)
insertintosaleinfo(salesman,salemonth,achievement)values('C','2月7日',70)
insertintosaleinfo(salesman,salemonth,achievement)values('C','3月2日',80)
insertintosaleinfo(salesman,salemonth,achievement)values('C','3月9日',90)
--显示数据
selectsalesmanas业务员,salemonthas月份,achievementas业绩fromsaleinfo
declare@sqlvarchar(8000)
set@sql='selectsalesmanas业务员'
select@sql=@sql+',sum(caseleft(salemonth,2)when'''+left(salemonth,2)+'''thenachievementelse0end)['+left(salemonth,2)+']'
from(selectdistinctleft(salemonth,2)assalemonthfromsaleinfo)asa
set@sql=@sql+',sum(achievement)as业绩fromsaleinfogroupbysalesman'
exec(@sql)