Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 avoid duplicate sales trx

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-10 : 04:05:23
Dear Sir, Anyone can help me ?
I have data in below but expect the output result is like this. i need to sum up the redemption amount and not allow duplicate sales order.

I am trying below query but not work , the sales order number is still repeat show in report.
-------------------------------------------------------
select distinct sales_order , sales_amt,sum(redemption_amt) from price
group by sales_order
-------------------------------------------------------
sales_order sales_amt redemption amount
SO_0008 $1000 $500
S0_0008 $1000 $300
S0_0008 $1000 $200
-------------------------------------------------------
Desire output
sales_order sales_amt redemption amount
SO_0008 $1000 $1000

Please give me one query for this output ?


Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2015-02-10 : 07:21:25
This:

--Create table
create table sales_AMT
(
sales_order VARCHAR(20),
sales_amt INT,
redemption_amount INT
)
--insert values
insert into sales_AMT VALUES ('SO_0008', 1000, 500)
insert into sales_AMT VALUES ('SO_0008', 1000, 300)
insert into sales_AMT VALUES ('SO_0008', 1000, 200)

--query to sum redemption amount
select s.sales_order, s.sales_amt, sum(s.redemption_amount) SumRedemption
from sales_AMT s
group by s.sales_order, s.sales_amt

--result
sales_order sales_amt SumRedemption
=========== ========= =============
SO_0008 1000 1000
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-10 : 09:41:39
Grifer,pls help to look this query it is writing right ?

let me to explain more detail. My data is already save into table.
The record is below:
sales_order sales_amt redemption amount
SO_0008 $1000 $500
S0_0008 $1000 $300
S0_0009 $1500 $1200
SO_0009 $1500 $300
S0_0018 $1700 $1400
S0_0018 $1700 $300

it is using your below query to get above result ? i hope the result just display by each sales number with their sum redemption amt like
this :
S0_0008 $ 800
S0_0009 $1500
S0_0018 $1700
----------------------------------------
select s.sales_order, s.sales_amt, sum(s.redemption_amount) SumRedemption,employee.staff_position

from sales_AMT s
inner join employee.staff_id= s.staff_id
group by s.sales_order, s.sales_amt
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2015-02-10 : 11:37:32
Not sure what you are trying to tell me with your last message? Can you explain what data you want to see?
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-10 : 23:00:04
Anyone can help ?
Currentdata

column1 col2 col3
trx_no sell redemption
SO_0008 $1000 $500
S0_0008 $1000 $300

S0_0009 $1500 $1100
SO_0009 $1500 $300

S0_0018 $1700 $1400
S0_0018 $1700 $300

Expect data
column1 col2 col3
trx_no sell redemption
S0_0008 $1000 $ 800
S0_0009 $1500 $1400
S0_0018 $1700 $1700

Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2015-02-11 : 05:15:07
select
trx_no,
min(sell) as sell,
sum(redemption) as redemption
from YourTable
group by trx_no


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2015-02-11 : 07:05:29
quote:
Originally posted by usafelix

Anyone can help ?
Currentdata

column1 col2 col3
trx_no sell redemption
SO_0008 $1000 $500
S0_0008 $1000 $300

S0_0009 $1500 $1100
SO_0009 $1500 $300

S0_0018 $1700 $1400
S0_0018 $1700 $300

Expect data
column1 col2 col3
trx_no sell redemption
S0_0008 $1000 $ 800
S0_0009 $1500 $1400
S0_0018 $1700 $1700



USAFelix my reply earlier works, I have updated my table with the data you have and ran the query. Results below:


--insert values
insert into sales_AMT VALUES ('SO_0008', 1000, 500)
insert into sales_AMT VALUES ('SO_0008', 1000, 300)
insert into sales_AMT VALUES ('SO_0009', 1500, 1100)
insert into sales_AMT VALUES ('SO_0009', 1500, 300)
insert into sales_AMT VALUES ('SO_0018', 1700, 1400)
insert into sales_AMT VALUES ('SO_0018', 1700, 300)

--Query to sum redemption amount but keep sales amt value


--query to sum redemption amount
select s.sales_order, s.sales_amt, sum(s.redemption_amount) SumRedemption
from sales_AMT s
group by s.sales_order, s.sales_amt

sales_order sales_amt SumRedemption
----------- --------- -------------
SO_0008 1000 800
SO_0009 1500 1400
SO_0018 1700 1700
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 04:20:10
it should be work . Thanks
Go to Top of Page
   

- Advertisement -