selecting a value when at least one returned row is a specific value

Database
Fan

selecting a value when at least one returned row is a specific value

I'm fairly new in SQL and am wanting a query.

 

I have a table that might look like this

 

Store  Order  Date  Transaction Type  Amount

1   12345   10/29/17    1     $0

1   66666   10/30/17    1     $10

1   66666    10/30/17    2     -$10

 

I'm trying to sum the results for a given store/order/date combination and then state whether or not there was a '2' transaction type (refund). So I'm looking for results like this:

 

Store  Order  Date  Amount   Refund

1   12345   10/29/17   $0        No

1   66666   10/30/17   $0        Yes

 

So I believe to get the sum I can to a sum() over partition by using the store/order/date to ignore the transaction type but where I don't understand is how I can make a 'yes'/'no' type result if a '2' exists or not. Here a '2' means there was a refund and a '1' means there was a sale. So really for any amount that sums to zero I'm trying to figure out if it was sold for zero or whether there was a refund that has made it zero. (though I realize if it was first sold for zero and refunded the results will make it appear the refund caused the zero). The date doesn't mean the refund date so while refunds might occur on a different day it is still aligned under the same date as the sale.

 

I don't completely know the data so I didn't want to count the number of orders and then assume whever the count was greater than 1 there must have been a refund involved. I guess I could make an initial quey without the transaction type then join it to a sub-query that looks for only transaction type = '2' and if it exists then return a true/false type response but there are millions of records and I was looking for the most efficient way. Any thoughts on the best way to accomplish this?


Accepted Solutions
Teradata Employee

Re: selecting a value when at least one returned row is a specific value

Oops! I don't even see any need to use analytical functions.  Simply:

 

Select Store, Order, Date, Sum(Amount),
CASE Max(Transaction Type) When 1 then 'No' Else 'Yes' END as Refund
From ...
Group by 1, 2, 3

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: selecting a value when at least one returned row is a specific value

Try:
Select Store, Order, Date,
Sum(Amount) over( partition by Store, Order, Date ) as Amount
CASE Max(Transaction Type) over( partition by Store, Order, Date )
    When 1 then 'No' Else 'Yes' END as Refund
From ...

Teradata Employee

Re: selecting a value when at least one returned row is a specific value

Oops! I don't even see any need to use analytical functions.  Simply:

 

Select Store, Order, Date, Sum(Amount),
CASE Max(Transaction Type) When 1 then 'No' Else 'Yes' END as Refund
From ...
Group by 1, 2, 3

Fan

Re: selecting a value when at least one returned row is a specific value

Perfect, after looking through some of the results and anomolies like values that also have refunds, I think this works nicely!