CODEWITHSUNDEEP
sqldatabaseoracle-databasegroup-by
SheerSt
SheerSt

Reputation: 3337

Oracle SQL - filter group by in AVG function

I query the 'sales' table for 'price', grouping by product name:

SELECT product_name, sale_price, sale_date,
SUM(CASE WHEN sales.sale_date = TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (sale_price) ELSE 0 END) sale_yday,
SUM(CASE WHEN sales.sale_date = TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 THEN (sale_price) ELSE 0 END) sale_lweek,
AVG(sales.sale_price) ten_wk_avg,
STDDEV(sales.sale_price) ten_wk_stddev
FROM sales
WHERE sales.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 sales.product_name

The AVG function is not doing what I need, though - I need it to take an average of only the previous 10 weeks, not the previous 10 weeks PLUS today and yesterday like above. Same situation with standard deviation. Is there a better way to build this query, so that I can get ten_wk_avg and ten_wk_stddev as described?

Upvotes: 0

Views: 657

Answers (2)

TommCatt
TommCatt

Reputation: 5636

First, you should consider what you're asking for in the query. You want the product_name, sale_price and sale_date for every sale for the last 10 weeks. That could be dozens, hundreds or thousands of sales. Then you want (per product) yesterday's sales, last week's sales and the average and standard deviation of sales over the past 10 weeks. Those are single values for each product yet there they will be, repeated over and over again those dozens or hundreds or thousands of times. Is that really what you want?

Here is a query that will give you the aggregate values you want -- one line for each product. If you want to see it in action, try the SQL Fiddle workup. It may not be the most compact of queries, but it is broken up into chunks that should be easy to follow as to what they are doing.

WITH
By_Week AS(
  SELECT  product_name, sale_date, sale_price,
          trunc(sale_date, 'd') as week_of,
          (trunc(SYSDATE, 'd') - trunc(sale_date, 'd')) / 7 AS weeks_ago
  FROM    sales
),
Weekly_Sales AS(
  SELECT  product_name, 
          sum( CASE WHEN weeks_ago BETWEEN 1 AND 10 THEN sale_price END ) AS ten_wk_avg,
          stddev( case when weeks_ago between 1 and 10 then sale_price end ) AS ten_wk_stddev
  FROM    By_Week
  group by product_name
)
SELECT  w.product_name,
        sum( CASE WHEN trunc(SYSDATE) - w.sale_date = 1 THEN w.sale_price END ) AS sale_yday,
        sum( CASE WHEN weeks_ago = 1 THEN sale_price END ) AS sale_lweek,
        s.ten_wk_avg, s.ten_wk_stddev
FROM    By_Week w
JOIN    Weekly_Sales s
  ON    s.product_name = w.product_name
GROUP BY w.product_name, s.ten_wk_avg, s.ten_wk_stddev
order by w.product_name;

Btw, you have both Oracle and MySQL flagged. The query above is for Oracle. MySQL doesn't have CTEs (iirc) but you can put them in as inline views.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use conditional aggregation:

SELECT product_name, sale_price, sale_date,
       SUM(CASE WHEN sales.sale_date = TO_DATE('14-JUN-14', 'DD-MON-YY') - 1 THEN (sale_price) ELSE 0 
           END) as sale_yday,
       SUM(CASE WHEN sales.sale_date = TO_DATE('14-JUN-14', 'DD-MON-YY') - 7 THEN (sale_price) ELSE 0 
           END) as sale_lweek,
       AVG(CASE WHEN sales.sale_date < TO_DATE('14-JUN-14', 'DD-MON-YY') - 1 THEN sales.sale_price
           END) as ten_wk_avg,
       STDDEV(CASE WHEN sales.sale_date < TO_DATE('14-JUN-14', 'DD-MON-YY') - 1 THEN sales.sale_price
              END) as ten_wk_stddev

. . .

avg() and stddev() ignore NULL values, which is why there is no else clause in the case.

Upvotes: 1

Related Questions