Select Distinct Column then sum it in store procedure ?

Question

4.00/5 (1 vote)

See more:

VB

VoucherID              amount
   1                     5
   1                     5
   2                     2
   3                     7
   3                     7
   3                     7

Output needed like below:

XML

amount
  14

*** take 5+2+7=14

Posted 2-Apr-14 20:46pm

Shahnawazcode

Add a Solution

4 solutions

Solution 2

SQL

with cte (voucherid, amount)
as
(
  select Distinct voucherid, amount from table1
)select sum(amount) as amount from cte

Posted 2-Apr-14 21:02pm

Peter Leow

Comments

King Fisher 3-Apr-14 3:12am

;)

Solution 3

go with solution1:
else

SQL

select sum(t1.amount) from(select distinct * From sample1 )t1 left join  (select distinct *From sample1) t2 on t1.id=t2.id and t1.amount=t2.amount

Posted 2-Apr-14 21:18pm

King Fisher

Solution 4

SQL

Select voucherID, SUM(AMOUNT) FROM
(
    SELECT Row_Number() over(partition by voucherId, Amount group by voucherID, Amount) as rownr,
	voucheriD, amount
) as collection
where rownr = 1

Posted 2-Apr-14 22:15pm

Herman<T>.Instance

Add a Solution

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

AndrewCharlz · Accepted Answer · 2014-04-02T20:55:00

Solution 1

SQL

select VoucherID, Sum(Amount) from ((select Distinct VoucherID, amount from table)) xx

Posted 2-Apr-14 20:55pm

AndrewCharlz

Comments

Shahnawazcode 3-Apr-14 3:42am

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[USP_GetDealerReceiptTotalByExecutiveID]

@FromDate datetime,
@ToDate datetime,
@ExecutiveID int

AS
SET NoCount On
BEGIN

select s.VoucherID, Sum(s.Amount) from ((select Distinct s.VoucherID, s.Amount from dbo.VoucherDetail))

FROM dbo.VoucherEntry d,
dbo.VoucherDetail s

WHERE d.TranDate BETWEEN @FromDate AND @ToDate
AND s.ReceivedToUsersID=@ExecutiveID
AND d.ID=s.VoucherID

END

This is my store procedure
But it shows error "Incorrect syntax near the keyword 'FROM'."

AndrewCharlz 3-Apr-14 3:51am

select s.VoucherID, Sum(s.Amount) from (
select Distinct s.VoucherID, s.Amount from dbo.VoucherDetail s
left outer join dbo.VoucherEntry d on d.ID=s.VoucherID
where s.ReceivedToUsersID=@ExecutiveID
and d.TranDate BETWEEN @FromDate AND @ToDate ) xx

Shahnawazcode 3-Apr-14 4:01am

Thanks for your reply....

what is the meaning of xx?
I have put "select s.VoucherID, Sum(s.Amount) from ( select Distinct s.VoucherID, s.Amount from dbo.VoucherDetail s left outer join dbo.VoucherEntry d on d.ID=s.VoucherID where s.ReceivedToUsersID=@ExecutiveID and d.TranDate BETWEEN @FromDate AND @ToDate )"

it shows the following error
Incorrect syntax near the keyword 'END'.

AndrewCharlz 3-Apr-14 5:03am

u can use any alias for that sub query without that ull get error

Shahnawazcode 3-Apr-14 5:38am

Can you give me the complete query from this.. This will helpfull for me..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[USP_GetDealerReceiptTotalByExecutiveID]

@FromDate datetime,
@ToDate datetime,
@ExecutiveID int

AS
SET NoCount On
BEGIN

select s.VoucherID, Sum(s.Amount) from ( select Distinct s.VoucherID, s.Amount from dbo.VoucherDetail s left outer join dbo.VoucherEntry d on d.ID=s.VoucherID where s.ReceivedToUsersID=@ExecutiveID and d.TranDate BETWEEN @FromDate AND @ToDate )

--FROM dbo.VoucherEntry d,
-- dbo.VoucherDetail s

--WHERE d.TranDate BETWEEN @FromDate AND @ToDate
--AND s.ReceivedToUsersID=@ExecutiveID
--AND d.ID=s.VoucherID
--distinct s.VoucherID

END

AndrewCharlz 3-Apr-14 6:23am

without alias query wont work y did u remove that xx

Shahnawazcode 3-Apr-14 6:37am

after given alias

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[USP_GetDealerReceiptTotalByExecutiveID]

@FromDate datetime,
@ToDate datetime,
@ExecutiveID int

AS
SET NoCount On
BEGIN

select s.VoucherID, Sum(s.Amount) from ( select Distinct s.VoucherID, s.Amount from dbo.VoucherDetail s left outer join dbo.VoucherEntry d on d.ID=s.VoucherID where s.ReceivedToUsersID=@ExecutiveID and d.TranDate BETWEEN @FromDate AND @ToDate ) as ReciptAmount

--FROM dbo.VoucherEntry d,
-- dbo.VoucherDetail s

--WHERE d.TranDate BETWEEN @FromDate AND @ToDate
--AND s.ReceivedToUsersID=@ExecutiveID
--AND d.ID=s.VoucherID
--distinct s.VoucherID

END

error produce

Msg 207, Level 16, State 1, Procedure USP_GetDealerReceiptTotalByExecutiveID, Line 13
Invalid column name 'Amount'.
Msg 4104, Level 16, State 1, Procedure USP_GetDealerReceiptTotalByExecutiveID, Line 13
The multi-part identifier "s.VoucherID" could not be bound.
Msg 4104, Level 16, State 1, Procedure USP_GetDealerReceiptTotalByExecutiveID, Line 13
The multi-part identifier "s.Amount" could not be bound.

AndrewCharlz 3-Apr-14 6:53am

select VoucherID, Sum( Amount) from ( select Distinct s.VoucherID, s.Amount from dbo.VoucherDetail s left outer join dbo.VoucherEntry d on d.ID=s.VoucherID where s.ReceivedToUsersID=@ExecutiveID and d.TranDate BETWEEN @FromDate AND @ToDate ) as ReciptAmount

AndrewCharlz 3-Apr-14 6:54am

now this should work

AndrewCharlz 3-Apr-14 6:54am

its just because

voucher id and sum(amount) belong to ReciptAmount

Shahnawazcode 3-Apr-14 7:00am

Invalid column name 'Amount'.

Shahnawazcode 5-Apr-14 2:11am

hi I am putting this...

select VoucherID, Sum(Amount) from ( select Distinct s.VoucherID, d.Amount from dbo.VoucherDetail s left outer join dbo.VoucherEntry d on d.ID=s.VoucherID where s.ReceivedToUsersID=@ExecutiveID and d.TranDate BETWEEN @FromDate AND @ToDate ) as TotalReceiptAmount

but it shows error:
Msg 8120, Level 16, State 1, Procedure USP_GetDealerReceiptTotalByExecutiveID, Line 16
Column 'TotalReceiptAmount.VoucherID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I think you can solve it. Please help me...

AndrewCharlz 5-Apr-14 4:55am

select VoucherID, Sum(Amount) from ( select Distinct s.VoucherID, d.Amount from dbo.VoucherDetail s left outer join dbo.VoucherEntry d on d.ID=s.VoucherID where s.ReceivedToUsersID=@ExecutiveID and d.TranDate BETWEEN @FromDate AND @ToDate ) as TotalReceiptAmount
group by VoucherID

Shahnawazcode 5-Apr-14 5:36am

Thank you very much...!!! :-)

AndrewCharlz 5-Apr-14 5:44am

welcome

Select Distinct Column then sum it in store procedure ?

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Select Distinct Column then sum it in store procedure ?

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us