oracle sql - filter out duplicate entries on condition

Question

I have the following query to yield sale_price by date:

SELECT 
product_name, 
SUM(sale_price) top_sale_price, 
sale_date,
COUNT(*) count
FROM sales
WHERE sale_date IN
    (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
    TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7)
GROUP BY 
product_name, 
sale_date

My results look something like this:

product_name     top_sale_price     sale_date     count
shoes            10.00              01-JUL-14     2
hat              11.00              30-JUN-14     1
shirt            12.00              24-JUN-14     3
...

I want to select only a single shirt or hat from each grouping, based on some logic. For example, say that there is a 'is_valid_purchase' row. If there is one or more item in the grouping, like in 'shoes' and 'shirt', I want to select the item with 'is_valid_purchase' set to true (note that in this example, only one of the items in the group by will have 'is_valid_purchase' set to true). How could I modify my sql to do what I've described?

Answer 1

Is this what you want?

with t as (
      SELECT product_name, SUM(sale_price) top_sale_price, sale_date, COUNT(*) count
      FROM sales
      WHERE sale_date IN
          (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
          TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7) and
         is_valid_purchase = 1
      GROUP BY product_name, sale_date
     )
select *
from t;