How to do left join with nulls

Question

I'm trying to do a self join and subtract values of the same column.

--sum of all pageviews of circleid 0 of each device category - (sum of all 
--pageviews of circleid <> 0 of each device category)

Existing table - gadata:

PageName    DeviceCategory  FKCircleId  FKGAFilterKeyID PageViews
login       desktop         0            5              10
login       desktop         0            5              20
login       mobile          0            5              5
login       tablet          0            5             15
login       desktop         1            4              2
login       desktop         1            4              2
login       mobile          1            4              3
login       tablet          1            4              4

Desired o/p:

PageName    DeviceCategory  PageViews
login       desktop         26 --(30-4)
login       mobile           2 --(5-3)
login       tablet           11 --(15-4)

But this query gives me null values

PageName DeviceCategory Circle  Total   
Login    desktop    NULL    NULL
Login    mobile     NULL    NULL
Login    tablet     NULL    NULL

CREATE TABLE gadata(PageName varchar(10),DeviceCategory 
varchar(10),FKCircleId int, FKGAFilterKeyID int,PageViews int)
insert into gadata values('login','desktop',0,5,10)
insert into gadata values('login','desktop',0,5,20)
insert into gadata values('login','mobile',0,5,5)
insert into gadata values('login','tablet',0,5,15)
insert into gadata values('login','desktop',1,4,2)
insert into gadata values('login','desktop',1,4,2)
insert into gadata values('login','mobile',1,4,3)
insert into gadata values('login','tablet',1,4,4)

    ;WITH TBL3 AS(      
    SELECT gd.PageName,gd.DeviceCategory,'All' AS Circle,
           SUM(gd.PageViews) AS Total1,gd.FKGAFilterKeyID
    FROM gadata(NOLOCK) gd          
    WHERE gd.FKGAFilterKeyID =5     
    GROUP BY gd.PageName,gd.DeviceCategory,gd.FKGAFilterKeyID
    ),
    TBL4 AS(        
    SELECT gd.PageName,gd.DeviceCategory,'Other' AS t4Circle,
           ISNULL(SUM(gd.PageViews),0) AS Total2,gd.FKGAFilterKeyID
    FROM gadata(NOLOCK) gd      
    WHERE gd.FKGAFilterKeyID <> 5       
    GROUP BY gd.PageName,gd.DeviceCategory,gd.FKGAFilterKeyID
    )
    SELECT t3.PageName,t3.DeviceCategory,t4.t4Circle AS Circle,
           (t3.Total1-t4.Total2) AS Total
    FROM TBL3 t3
    LEFT JOIN TBL4 t4 ON t3.FKGAFilterKeyID = t4.FKGAFilterKeyID 
AND t3.DeviceCategory= t4.DeviceCategory

When I run the subQueries in tbl3 & tbl4 individually, I get the data. Where am I going wrong.

sqlfiddle isn't working. I would have given a fiddle.

Answer 1

WITH cte1 
AS
(SELECT [PageName],[DeviceCategory],sum([PageViews]) smx
FROM [dbo].[gadata]
WHERE [FKCircleId]=0
GROUP BY [PageName],[DeviceCategory])
,cte2
AS
(SELECT [PageName],[DeviceCategory],sum([PageViews]) smx1
FROM [dbo].[gadata]
WHERE [FKCircleId]!=0
GROUP BY [PageName],[DeviceCategory])
--SELECT * FROM cte1
SELECT c1. [PageName],c1.[DeviceCategory],smx-smx1 as Total
FROM cte2 AS c2 JOIN cte1 AS c1
ON c2.[DeviceCategory]=c1.[DeviceCategory]

Answer 2

instead of the long query in question just use a self join only:

select tbl1.pagename, tbl1.devicecategory, tbl1.pageviews - tbl2.pageviews
from (select t2.pagename, t2.devicecategory, sum(t2.pageviews) as pageviews
       from gadata t2
       where t2.FKCircleId=0 
       group by t2.pagename, t2.devicecategory) tbl1
join (select t3.pagename, t3.devicecategory, sum(t3.pageviews) as pageviews
       from gadata t3
       where t3.FKCircleId <> 0 
       group by t3.pagename, t3.devicecategory) tbl2
on tbl1.pagename=tbl2.pagename and tbl1.devicecategory=tbl2.devicecategory

OUTPUT:

login       desktop         26 
login       mobile           2 
login       tablet           11 

SQLFIDDLE DEMO

Answer 3

You are joining on incorrect columns. Also use ISNULL function in order not to get NULLs when no matching rows will be found in TBL4:

SELECT t3.PageName,t4.t4Circle AS Circle,
       (t3.Total1-ISNULL(t4.Total2, 0)) AS Total
FROM TBL3 t3
LEFT JOIN TBL4 t4 ON t3.PageName = t4.PageName AND t4.DeviceCategory = t3.DeviceCategory

Output:

PageName    Circle  Total
login       Other   26
login       Other   2
login       Other   11

I have deleted AccessType column, because it was not in create script.

EDIT:

I also don't know your logic but if you can have in TBL4 values that are not in TBL3(for example login, windows phone, 1, 10 ) then you can use FULL OUTER JOIN:

insert into gadata values('login','win. phone',1,10,6)

SELECT ISNULL(t3.PageName, t4.PageName) AS PageName,t4.t4Circle AS Circle,
       (ISNULL(t3.Total1, 0)-ISNULL(t4.Total2, 0)) AS Total
FROM TBL3 t3
FULL JOIN TBL4 t4 ON t3.PageName = t4.PageName AND t4.DeviceCategory = t3.DeviceCategory

Output:

PageName    Circle  Total
login       Other   52
login       Other   4
login       Other   22
login       Other   -6