title: Get Sales Commoditywise in SQL SERVER
body: <p>I want to present commodity wise sales for the year. But all commodities should be shown but the sales is presented for those commodities which are belonged the particular year and the remaining commodities just presented with null values but the year should be the selected year. </p>
  
  <p>I have below data.</p>
  
  <pre><code>Declare @table table (Commodity Varchar(10),Sales int,YearOfSale INT)
  INSERT INTO @Table
  Values('Samsung',300,2020)
  ,('OPPO',500,2020)
  ,('NOKIA',700,2020)
  ,('OPPO',1000,2020)
  ,('REDMI',100,2029)
  ,('KORBON',90,2019)
  </code></pre>
  
  <p>I want the below result.</p>
  
  <pre><code>Commodity   TOTALSALES  YearOfSale
  ----------------------------------
  OPPO            1500    2020
  NOKIA           700     2020
  Samsung         300     2020
  REDMI           NULL    2020
  KORBON          NULL    2020
  </code></pre>
  
  <p>I have tried the below</p>
  
  <pre><code>;WITH Commodity
  AS (
      SELECT DISTINCT Commodity
          ,YearOfSale
      FROM @Table
      )
      ,Sales
  AS (
      SELECT Commodity
          ,SUM(Sales) Sales
          ,YearOfSale
      FROM @Table
      WHERE YearOfsale=2020
      GROUP BY Commodity
          ,YEAROfsale
      )
  SELECT P.Commodity
      ,CASE 
                  WHEN Sales IS NOT NULL
                      THEN Sales 
                  END TOTALSALES
      ,P.YearOfSale
  FROM Commodity P
  LEFT JOIN Sales QS ON P.Commodity = QS.Commodity
  ORDER BY Sales DESC
  </code></pre>
  
  <p>I am getting year 2019 but I need to present 2020.
  I would really appreciate a bit of help.</p>