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?

After aggregation you are not dealing with original individual rows any more. So what do you really want? Do you want to exclude invalid purchases from `SUM` and `COUNT`? — PM 77-1, Jul 04 '14 at 01:39
yes, I want to exclude from 'SUM' and 'COUNT' based on a condition. — SheerSt, Jul 04 '14 at 02:01
Then just include it in your `WHERE` clause: `sale_date IN ... AND is_valid_purchase`. — PM 77-1, Jul 04 '14 at 02:03

Gordon Linoff · Accepted Answer · 2014-07-04T03:05:40.170

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;

Strange syntax with double `WHERE` clause. – PM 77-1 Jul 04 '14 at 03:02 — PM 77-1, Jul 04 '14 at 03:02

oracle sql - filter out duplicate entries on condition

1 Answers1