sum子句用别名来计算总数？ [英] sum clause method calculating the total with alias name?

问题描述

ALTER PROCEDURE [dbo].[K_FS_GetFeedDivisionReport] 
@orderno varchar(50)
AS
BEGIN

SET NOCOUNT ON;
select OT.OrderNo,FT.Feedtype,ModeofPack='Plastic',BG.Bagtype,OD.Numofbags,   
(OD.numofbags*BG.Bagtype) as TotalQty,sum(TotalQty) as Total from  
K_FS_OrderconfirmDetails OD
inner join K_FS_bagtype BG on OD.bagtype=BG.sno
inner join k_FPS_FeedType FT  on FT.sno=OD.feedtype
inner join K_FS_OrderDetails OT on OT.sno = OD.orderno where OD.orderno = @orderno
group by  OT.OrderNo,FT.Feedtype,BG.Bagtype,OD.Numofbags
END

这里我计算TotalQty，我想计算TotalQty的总和但TotalQty是别名，是否可能？其他怎么解决呢...请帮助我...

Here I calculate TotalQty,I want to calculate sum of TotalQty but TotalQty is a alias name Is it possible?other how can i solve it..please help me...

推荐答案

你不能使用别名，这与的方式有关选择SELECT -statement。请参阅此 wiki [ ^ ]的描述。



而不是使用别名再次使用完整表达式。

用

You cannot use the alias, this has to do with how the SELECT-statement is evaluated. See this wiki[^] for a description.

Instead of using the alias use the full expression again.
Replace

SUM(TotalQty)

 with 

* BG.Bagtype）

SUM(OD.numofbags*BG.Bagtype)

ALTER PROCEDURE [dbo].[K_FS_GetFeedDivisionReport]
    @orderno varchar(50)
    AS
    BEGIN

    SET NOCOUNT ON;
    select OT.OrderNo,FT.Feedtype,ModeofPack='Plastic',BG.Bagtype,OD.Numofbags,
    (OD.numofbags*BG.Bagtype) as TotalQty,
    Total = (Select Sum (OD.numofbags*BG.Bagtype) from  K_FS_OrderconfirmDetails OD
    inner join K_FS_bagtype BG on OD.bagtype=BG.sno
    inner join k_FPS_FeedType FT  on FT.sno=OD.feedtype
    inner join K_FS_OrderDetails OT on OT.sno = OD.orderno where OD.orderno = @orderno)

 from
    K_FS_OrderconfirmDetails OD
    inner join K_FS_bagtype BG on OD.bagtype=BG.sno
    inner join k_FPS_FeedType FT  on FT.sno=OD.feedtype
    inner join K_FS_OrderDetails OT on OT.sno = OD.orderno where OD.orderno = @orderno
    group by  OT.OrderNo,FT.Feedtype,BG.Bagtype,OD.Numofbags,OD.numofbags*BG.Bagtype
    END

这篇关于sum子句用别名来计算总数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！