CODEWITHSUNDEEP
sqlpostgresql
Jake Wagner
Jake Wagner

Reputation: 826

What join to use in this case?

I have our yearly sales thus far for 2017 in the query marked as Yearly and our all time Inventory marked as AllTime. I need an advice on what join I could use or what additional code I could add to my already script which would get me to show the value marked as (null) in the result. The (null) values represents Robin from the Yearly table but since I do not have that value in the AllTime query it is displayed as null in the result. If I change the order of the resultant query to from yearly left outer join alltime, Red Arrow and Captain Marvel both lose their respected spots since both have zero values in the Yearly query. If I change to from alltime left outer join yearly, the null value (Robin) goes away. Please share your advice. And as always, if you require additional details, let me know please. 

with AllTime as 

(select dp.Builder,
count(*) "My Inventory" from alldatainput dp
where Project_ID = 'GAP'
group by rollup ((dp.Builder))           

),

Yearly as 

(                   

select Builder,(count
(Sale_Date) filter (where extract(year from Sale_Date) = 2017 
and Project_ID = 'GAP'))
 - (count(Cancelled) filter (where extract(year from Cancelled) = 2017 
and Project_ID = 'GAP')) as "Net for 2017"
from allsalesdata sd
and Project_ID = 'GAP'
group by rollup((Builder))
having (count(Sale_Date) filter (where extract(year from Sale_Date) = 2017 
and Project_ID = 'GAP' ))
- (count(Cancelled) filter (where extract(year from Cancelled)   = 2017 and 
sd.Project_ID = 'GAP')) > 0
)

select alltime.Builder,coalesce(yearly."Net for 2017",0) 
as "YTD Home Sales", coalesce(alltime."My Inventory",0) as "My Inventory"
from allTime
full join yearly on allTime.Builder = yearly.Builder 
group by rollup((allTime.Builder,yearly."Net for 2017",alltime."My Inventory",
yearly.Builder))
order by yearly.Builder;

Result: 

  Builder           YTD Home Sales    My Inventory
  Batman                  3             86
  Superman                5             26
  Aquaman                 3             29
  Martian                 6             84
  Green Lantern           2             21
  Wonder Woman            1              3
  Flash                   2             74
  ****(null)              4              0   ************
  Cyborg                  2             54
  Batwing                 5             25
  Captain Marvel          0             15
  Red Arrow               0              1
                          33            0
                          0             418

Upvotes: 0

Views: 46

Answers (1)

htf
htf

Reputation: 143

select Builder, sum(x."YTD Home Sales") as "YTD Home Sales",
sum(x."My Inventory") as "My Inventory"
from (
select Builder, yearly."Net for 2017" as "YTD Home Sales",
0 as "My Inventory"
from yearly
UNION
select Builder, 0 as "YTD Home Sales",
alltime."My Inventory"
from alltime
) x 
group by Builder
order by Builder;

Upvotes: 1

Related Questions