CODEWITHSUNDEEP
mysqlsqljoingroup-byaggregate-functions
Saad
Saad

Reputation: 1360

SQL: aggregate function in select statement with a join involving group by clause

I have two tables with following attributes:

t1                               t2
source    domain     date           rank     domain     date 
---       ---        ---            ---      ---        ---
A        google    2008-10-24        1      google    2007-10-20
A        facebook  2010-05-17        1      google    2009-02-21
B        google    2007-05-14        2      facebook  2008-04-13
B        google    2012-01-05        2      facebook  2012-09-12
B        facebook  2014-03-23        2      facebook  2015-04-01

I am trying to find the date difference b/w the first(earliest) occurrence of each domain in t2 with their first occurrence (based on date) in t1.

The following query is giving me the required result:

select t2.domain, datediff(min(t1.date),min(t2.date)) 
from t1 join t2 
on t1.domain = t2.domain 
group by t2.domain

Now, if I want to compute the average of date difference across all domains (only for their first occurrence), this part gives an error in select statement (rest of the query is same as above):

select avg(datediff(min(t1.date),min(t2.date))) ...

Any idea how to correct the error (Invalid use of group function) here?
Also, I want to compute the average date difference group by source. Such that the output should be like: 

source     avg_datediff 
---        ---
A          some_value 
B          some_value

Just like above, for multiple same domains in a given source, I need to consider the date of earliest domain for that source only, when computing avg date difference.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use a subquery:

select avg(days)
from (select t2.domain, datediff(min(t1.date), min(t2.date)) as days 
      from t1 join
           t2 
           on t1.domain = t2.domain 
      group by t2.domain
     ) tt;

Upvotes: 2

Related Questions