如何获得表中特定列的总和 [英] how to get sum of particular column in the table

问题描述

declare @Month int='2';
declare @Year nvarchar(500)='2014';

select distinct(Emp_Status)as EMPSTATUS,
       sum(case when Att_Status in ('AB') then 1 else (case when( Att_Status like '%/AB%') then 0.5 else case when( Att_Status like '%AB/%') then 0.5 else 0 end end) end) as ABSENT_DAYS ,
        DATEDIFF(DAY,
            DATEADD(DAY, 0, DATEADD(month, ((@Year - 1900) * 12) + @Month - 1, 0)),
            DATEADD(DAY, 0, DATEADD(month, ((@Year - 1900) * 12) + @Month, 0))
       ) AS [ACTUAL DAYS]
       from Attendence 
       inner join 
        EmployeeMaster  on fk_Att_EmpCode=pk_Emp_Code
where year(Att_Date)=@Year and Month (Att_Date)=@Month
 group by Emp_Status</blockquote>

和上述查询的输出格式是

and output format of that above query is

EMPSTATUS	ABSENTDAYS	ACTUALDAYS
Direct Contract	5.0	28
Permanent	48.5	28

但是我希望总共将剩余时间作为总额

而且我想要总计ACTUALDAYS



表示以下格式

but i want out put total of ABSENTDAYS as total
and I dnt Want Total of ACTUALDAYS

means bellow Format

EMPSTATUS	ABSENTDAYS	ACTUALDAYS
Direct Contract	5.0	28
Permanent	48.5	28
total	53.5	28

推荐答案

首先，这种格式和表示类型的东西不是问题一个问题。查询为您提供数据，其余部分由表示层（无论何种类型）决定。所以采取这条道路是一个糟糕的设计决定。如果这是一个严肃的BI应用程序，你将在它的生命周期中遇到可维护性问题。



无论如何，如果你坚持这种方法，将有在最后添加一个UNION，需要有相同的底层查询，但没有GROUP BY。



我没有尝试重新创建你的数据，所以检查这个解释性示例：

First of all, such formatting and presentation kind of stuff is not a matter of a query. A query gives you the data, the rest is up to a presentation layer (whatever kind of). So it is a bad design decision to take this path. If this is meant to be a serious BI application, you will encounter maintainability issues during it's life-cycle.

Wither way, if you stick to this approach, will have to add an UNION at the end which will need to have the same underlying query, but without a GROUP BY.

I have not tried to recreate your data, so check this explanatory example:

create table data(D char(1), A int);
insert into data values('A', 10);
insert into data values('A', 20);
insert into data values('B', 30);
insert into data values('B', 40);

select D, sum(A) from data group by D union select 'T', sum(A) from data;

在您的情况下看起来很难看。

但您可以使用视图或 CTE [ ^ ]使它更好一点：

That will look ugly in your case.
But you can use a view or CTE[^] to make it a little bit nicer:

with myCTE (myD, myA)
as (
  select D as myD, A as myA from data
)
select myD, sum(myA) from myCTE group by myD union select 'T', sum(myA) from myCTE;

您可以将所有计算都放在 AS（...） part，所以你不必在UNION子句之后重复它。

And you can put all your calculation inside the AS(...) part, so you don't have to repeat it after the UNION clause.

你好

Hi

Use the OVER clause to modify a SUM scope in your query. No GROUP BY needed

SELECT
    MyColumn, OtherColumn,
    SUM(MyColumn) OVER () AS SumTotal
FROM

可能会帮助你...尝试这个



从YourTable中选择EMPSTATUS，ABSENTDAYS，ACTUALDAYS

union

选择'Total'作为EMPSTATUS，（选择总和（ABSENTDAYS） ）来自YourTable）作为新闻日，（从YourTable中选择不同的ACTUALDAYS）作为'ACTUALDAYS'
来自YourTable的

may be this will help you... try this

select EMPSTATUS,ABSENTDAYS,ACTUALDAYS from YourTable
union
select 'Total ' as EMPSTATUS,(select sum(ABSENTDAYS) from YourTable) as ABSENTDAYS,(select distinct ACTUALDAYS from YourTable) as 'ACTUALDAYS'
from YourTable

这篇关于如何获得表中特定列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！