CODEWITHSUNDEEP
sqlsassubqueryaggregate-functionsproc-sql
Jackson
Jackson

Reputation: 405

PROC SQL subquery/correlated query

pro sql number;
   create table daily_total as
   select distinct trans_dt,
      sum((select (quantity * unit_price) 
           from transaction_detail 
           where subcat_num = 1111 and trans_dt = a.trans_dt))
   from transaction_detail as a
   where trans_dt between '2015-01-01' and '2015-01-02';
quit;

I understand I can achieve the samething with group by but I want to do this by subquery for the learning experierence.

I essentially want to select the two distinct dates and return the sum of each individual transaction for the subcategory on that particular day.

Thank you.

Upvotes: 0

Views: 574

Answers (1)

Parfait
Parfait

Reputation: 107567

In SQL, aggregate functions like SUM, AVG, MAX, MIN (depending on the SQL engine) do not run on subqueries themselves.

Consider the following adjustment where SUM is used inside the subquery. Also, I assume you want the subquery's date range to correspond to the outer query's date and one day in future. Hence, I use SAS's INTNX() function.

pro sql;
  create table daily_total as

  select distinct a.trans_dt,
    (select sum(b.quantity * b.unit_price)
     from transaction_detail As b
     where b.subcat_num = 1111  
     and (b.trans_dt between a.trans_dt 
          and intnx('day', a.trans_dt, 1)) As transaction_sum
   from transaction_detail a;

quit;

Upvotes: 1

Related Questions