Mysql right join with group by problem

Question

I have 2 tables:

• LandingPages - contains landing pages for each campaign.

• Reports - Contains hits and conversions to the landing page.

I’m trying to make a request that brings the sum of hits and conversions per landing page,

But I want that if the landing page does not receive any hits and conversions ( and does not appear in the report table ), then I want the result to return 0 as a result.

What am I doing so far:

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM Report c
RIGHT JOIN LandingPages l ON(c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x  
AND DayDate > 'y'
GROUP BY c.LandingPageId

The problem is that I only get rows with the landing page that exist in the report table and pass the date 'y',

(e.g.: I get only 2 rows of the landing page, but there are 4 landing pages

If I run this query, I get 4 results

SELECT l.LandingPageId FROM LandingPages l WHERE l.CampaignId = x 

)

( 0 ),

, , , , ?

.

update:

, , , , , :

:

 SELECT l.LandingPageId, IFNULL(SUM(Hits),0) AS Hits, IFNULL(SUM(PixelSum),0)  AS Conversion
    FROM LandingPages l
    LEFT JOIN Report c  ON( l.LandingPageId = c.LandingPageId) 
    WHERE (l.CampaignId = x OR  l.CampaignId IS NULL) 
    AND (DayDate > 'y' OR DayDate IS NULL)
    GROUP BY l.LandingPageId

!

!

Answer 1

,   !

:

Select L.LandingPageId
    , Coalesce( Sum( R.Hits ), 0 ) As Hits
    , Coalesce( Sum( R.PixelSum ), 0 ) As Conversion
From LandingPages As L
    Left Join Report As R
        On R.LandingPageId = L.LandingPageId
            And L.CampaignId = X
            And R.DayDate > 'y' 
WHERE L.CampaignId = X
Group By L.LandingPageId

Answer 2

. , . , ?

drop table landingpages;
create table landingpages (campaignid number, landingpageid number,  daydate number);

insert into landingpages values (1,100,20);
insert into landingpages values (1,101,21);
insert into landingpages values (2,102,20);
insert into landingpages values (2,103,21);

drop table report;
create table report (campaignid number, landingpageid number, hits number, pixelsum number);

insert into report values (1,100, 2, 1 );
insert into report values (2,102, 20, 21 );
insert into report values (2,103, 30, 31 );

commit;

SELECT c.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion  
    FROM landingpages c 
    LEFT JOIN report l ON(c.LandingPageId = l.LandingPageId ) 
    WHERE c.CampaignId = 1   
    AND DayDate > 19 
    GROUP BY c.LandingPageId 


LANDINGPAGEID       HITS CONVERSION
------------- ---------- ----------
          100          2          1
          101                      


2 rows selected.

, , . Oracle, mySQL, .

Answer 3

, Hits, PixelSum DayDate . ? . , ? . , DayDate , , , .

, ON . ON LandingPages. , Campaigns < > X DayDate <= 'y' (Btw, DayDate? DayDate > 'y' ), LandingPages.

, Coalesce IsNull, Coalesce ISO.

Select L.LandingPageId
    , Coalesce( Sum( ?.Hits ), 0 ) As Hits
    , Coalesce( Sum( ?.PixelSum ), 0 ) As Conversion
From LandingPages As L
    Left Join Report As R
        On R.LandingPageId = L.LandingPageId
            And R.CampaignId = X
            And ( R.DayDate > 'y' Or R.DayDate Is Null )
Group By L.LandingPageId

Left Joins, .

Answer 4

b/c . :

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM LandingPages l
LEFT JOIN Reports c ON(c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x  
AND DayDate > 'y'
GROUP BY c.LandingPageId

Answer 5

: WHERE c.CampaignId = x , ( ), , . c.CampaignId , c.CampaignId = x .

:

SELECT l.LandingPageId, SUM(Hits) AS Hits, SUM(PixelSum) AS Conversion 
FROM Report c
RIGHT JOIN LandingPages l ON(c.LandingPageId = l.LandingPageId )
WHERE (c.CampaignId = x  or c.CampaignId is null)  
AND DayDate > 'y'
GROUP BY l.LandingPageId

l.LandingPageId, c.LandingPageId null.

Answer 6

, ...

• , ( l.fieldname, c.fieldname) , , . , , 100%, , RIGHT JOIN, .
• when you set the criteria (WHERE c.CampaignID = something) in the table on the right, you turn it into an INNER JOIN. If you want to avoid this, add "... or c.CampaignID is null." Since the idea of ​​RIGHT join is that if there is a campaign identifier, you want it to be "x", but if there is no campaign, this is also normal. (truth?)

you cannot sum zeros, so I added coalesce to change zeros to zero.

SELECT 
   l.LandingPageId, 
   SUM(COALESCE(Hits,0)) AS Hits, 
   SUM(PixelSum) AS Conversion 
FROM 
      Report c
   RIGHT JOIN 
      LandingPages l 
   ON
      (c.LandingPageId = l.LandingPageId )
WHERE c.CampaignId = x OR c.CampaignID is null 
AND DayDate > 'y'
GROUP BY c.LandingPageId