sum clause method calculating the total with alias name?

Question

0.00/5 (No votes)

See more:

C#

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

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...

Posted 3-Jan-14 4:07am

Siva Hyderabad

Add a Solution

2 solutions

Solution 1

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

SQL

SUM(TotalQty)

with

SQL

SUM(OD.numofbags*BG.Bagtype)

Posted 3-Jan-14 4:12am

André Kraak

Updated 3-Jan-14 4:23am

v2

Comments

Siva Hyderabad 3-Jan-14 10:27am

yes,but i want to calculate the sum of "TotalQty" column ..i mean all rows of Totalqty

André Kraak 3-Jan-14 10:32am

It should do just that.

For each unique combination of the fields specified in the GROUP BY-clause the total value should be calculated.

thatraja 3-Jan-14 10:45am

He gave you right answer. Final query is

select OT.OrderNo,FT.Feedtype,ModeofPack='Plastic',BG.Bagtype,OD.Numofbags, SUM(OD.numofbags*BG.Bagtype) 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Siva Hyderabad · Accepted Answer · 2014-01-03T04:44:00

SQL

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