SQL issue pulling all tracking stats based off the email name

Question

I'm trying to pull together a DE that contains: total deployed, total opens, unique opens, total click, unique click, etc... There have been multiple deployments of the same email name and I'm trying to summerise the counts based on the jobid

when I run this code then opens part is duplicating, however, if I split the code it works. I can't quite work out what is wrong.

SQL Code

SELECT 
        t1.Total_Deployed,
        t1.jobid as t1_jobid,
        t2.Total_Opens,
        t2.jobid as t2_jobid,
        t3.Total_opens_Unique
FROM
(
SELECT 
COUNT(*) AS Total_Deployed,
jobid
FROM _Sent s
JOIN (select jobid as job
from _Job where emailname ='xxxxx') j on j.job = s.jobid 
WHERE s.jobid = j.job
group by jobid) as t1
cross join (SELECT
COUNT(*) as Total_opens,
jobid
FROM
(SELECT 
Eventdate,
row_number()over(partition by SubscriberKey order by eventdate asc) as row,
IsUnique,
SubscriberKey,
SubscriberID,
Jobid
FROM _open o
JOIN (select jobid as job
from _Job where emailname ='xxxxx') j on j.job = o.jobid 
WHERE o.jobid = j.job
GROUP BY eventdate, IsUnique, SubscriberKey,SubscriberID,jobid) x group by jobid) as t2
cross join (
SELECT
COUNT(*) as Total_opens_Unique,
jobid
FROM
(SELECT 
Eventdate,
row_number()over(partition by SubscriberKey order by eventdate asc) as row,
IsUnique,
SubscriberKey,
SubscriberID,
jobid
FROM _open o
JOIN (select jobid as job
from _Job where emailname ='xxxxx'
group by jobid) j on j.job = o.jobid 
WHERE o.jobid = j.job
GROUP BY eventdate, IsUnique, SubscriberKey,SubscriberID,jobid) x
Where x.row=1 group by jobid)
as t3

Answer 1

I'd recommend starting with the _Sent and _Job and then using the results in a second query to populate the other columns. The first query does an overwrite on the Email_Report data extension and the second just updates.

select
  j.jobid
, j.emailName
, count(*) Total_Deployed
from _Sent s
inner join _job j on j.jobid = s.jobid
where j.emailName = 'xxxxxx'
and s.eventDate >= convert(date, getDate()-90) /* previous 90 days */
/* target: Email_Report */
/* action: overwrite */

select
   x.jobid
 , sum(x.unique_opens) Total_opens_Unique
 , sum(x.opens) Total_Opens
from (
    select
      r.jobid
    , case 
        when o.isunique = 1 then 1 
        else 0 
      end unique_opens
    , case 
        when o.isunique = 0 then 1 
        else 0 
      end opens
    from Email_Report r
    inner join _Open o on o.jobid = r.jobid
) x
group by x.jobid
/* target: Email_Report */
/* action: update */

Also, the canned Recent Sending Summary Report may give you enough info without any Query Activities.