how u doing? I have a dataset of used cars store, and in the first query i have the total cars and respective car year. In the second query i have the cars that have been sold and respective car year. Third query is only the cars that have not been sold yet. My goal is to join these 3 columns.. I tried but the values just dont work, in the last query you can see what i tried to achieve it, but it returns me no registers :\

First Query - Total Cars Qty

Second Query - Sold Cars Qty

Thir Query - Not Sold Cars Qty

Fourth Query

CodePudding user response：

Your last query doesn't work, because you are using

where t3.sold = 'Y'
and t4.sold = 'N'

and a single Sales_ID can not be both sold an not sold. So you could remove these clauses from WHERE block and add them to JOIN ON conditions, like:

LEFT JOIN [Portfolio Project].dbo.Car_Detail t3
on t3.Sales_ID = t1.Sales_ID
and t3.sold = 'Y'

But actually, you don't need to use all three joins in this exact case. Try using cases inside of count() instead:

select  t2.[year], 
        count(t1.sold) as TOTAL_CAR_NUMBER,
        count(case when t1.sold = 'Y' then t1.sold else null end) as SOLD_CAR_NUMBER,
        count(case when t1.sold = 'N' then t1.sold else null end) as NOTSOLD_CAR_NUMBER,
FROM [Portfolio Project].dbo.Selling_Prrice t1
    INNER JOIN [Portfolio Project].dbo.Car_Detail t2
        on t1.Sales_ID = t2.Sales_ID
group by t2.[year]
order by t2.[year]