Home Ask Login Register

Developers Planet

Your answer is one click away!

AbuMariam February 2016

MySQL : Combinging Aggregate function with Date Time function in a join

Suppose I have two tables, one for probes and one for alien encounters that each probe makes..

 id           probe_launch_date
 --------     -----------------
 1            12/03/2214
 2            02/06/2215
 3            07/09/2215

id              encounter_species  encounter_date     probe_id
 -----------     ----------------   --------------     --------
 1                Klingons           01/06/2215         1
 2                Romulans           09/15/2215         2
 3                Vulcans            11/15/2215         2

I would like to see how many alien encounters each probe made since its launch date and I want the data grouped by the week since probe launch. So for example for probe 2 the result set might look like

  week  encounter
  ----  --------
  31     1
  40     1

Here is my first crack the SQL..

 SELECT WEEK(a.probe_launch_date) as launch_week, b.COUNT(*) 
  as num_of_encounters FROM probe a, encounter b  
  where a.id = b.probe_id and a.id = 2 GROUP BY  WEEK(a.probe_launch_date)

Of course this didn't work. How can I fix or re-write it?


Joe Stefanelli February 2016

EDIT based on feedback in comments:

The driving table should really be your encounter table, as those are the weeks you are interested in.

Use the DATEDIFF function to calculate the number of days between the launch date and encounter date, then divide by 7 and ROUND to get weeks.

SELECT ROUND(DATEDIFF(e.encounter_date, p.probe_launch_date) / 7, 0) AS week, 
        COUNT(*) AS number_of_encounters
    FROM encounter e
        INNER JOIN probe p
            ON e.probe_id = p.probe_id
    WHERE e.probe_id = 2
    GROUP BY ROUND(DATEDIFF(e.encounter_date, p.probe_launch_date) / 7, 0);

Post Status

Asked in February 2016
Viewed 3,566 times
Voted 6
Answered 1 times


Leave an answer

Quote of the day: live life