| 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 |
|
|
|
|
avoid duplicate sales trx