title: Calculate Count of Per Category of Products in SQL
body: <p>Assume these Tables:</p>
  
  <p><strong>Group</strong>: (Id, Title):  <code>{1,G1}, {2,G2}, {3,G3}, {4, G4}</code></p>
  
  <p><strong>Category</strong>: (Id, Title): <code>{1, Cat1}, {2, Cat2}, {3, Cat3}, {4, Cat4}</code></p>
  
  <p><strong>Product</strong>: (Id, GroupId, CategoryId, Name):</p>
  
  <pre><code>{1, 1, 1, G1C1P1},
  {2, 1, 2, G1C2P2},
  {3, 1, 2, G1C2P3},
  {4, 2, 2, G2C2P4},
  {5, 2, 2, G2C2P5},
  {6, 3, 1, G3C1P6},
  {7, 3, 3, G3C3P7}
  </code></pre>
  
  <p>So What I need is Count of any Category by Group for the above values is:</p>
  
  <pre><code>Group  Category  Count
  ----------------------
   G1     Cat1      1
   G1     Cat2      2
   G1     Cat3      0
   G1     Cat4      0
   G2     Cat1      0
   G2     Cat2      2
   G2     Cat3      0
   G2     Cat4      0
   G3     Cat1      1
   G3     Cat2      0
   G3     Cat3      1
   G3     Cat4      0
   G4     Cat1      0
   G4     Cat2      0
   G4     Cat3      0
   G4     Cat4      0
  </code></pre>
  
  <p>I try this:</p>
  
  <pre><code>SELECT 
          [GR].[Title] AS [Group],
          COUNT([PR].[Id]) AS [Count],
          [CA].[Title]
  
  FROM [dbo].[Group] AS [GR]
  FULL OUTER JOIN [dbo].[Product] AS [PR] ON [GR].[Id] = [PR].[GroupId]
  FULL OUTER JOIN [dbo].[Category] AS [CA] ON [PR].[CategoryId] = [CA].[Id]
  GROUP BY [CA].[Title], [GR].[Title];
  GO
  </code></pre>
  
  <p>But it's not the exact one, So What is your suggestion?</p>