avoid duplicate sales trx - SQL Server Forums

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

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

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?

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

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.

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

usafelix
Posting Yak Master

165 Posts

Posted - 2015-02-12 : 04:20:10

it should be work . Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources