Group By include joins in SQL Server

There is an application for reading articles or news from a database, and the users can select an article. The application will fetch the article content from the database, process it, and use text-to-speech to read it aloud.

Example Schema:

1. Articles
   • ArticleID (Primary Key)
   • Title
   • Content
   • AuthorID (Foreign Key to Authors)
   • CategoryID (Foreign Key to Categories)
   • Views (Number of views)
2. Authors
   • AuthorID (Primary Key)
   • Name
3. Categories
   • CategoryID (Primary Key)
   • Name

CREATE TABLE Articles (
    ArticleID INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(255),
    Content NVARCHAR(MAX)
);

INSERT INTO Articles (Title, Content)
VALUES
('The Future of AI', 'Artificial intelligence is rapidly evolving and is set to change the world...'),
('Health Benefits of Meditation', 'Meditation has been practiced for thousands of years...'),
('Technological Advancements in 2025', 'By 2025, new technologies will reshape the way we live and work...');

We will create a query to:

• Join the Articles, Authors, and Categories tables.
• Group the results by Category and Author.
• Filter the results using HAVING to show only articles with a minimum number of views and/or words.

• SELECT 
      a.AuthorID,
      au.Name AS AuthorName,
      c.CategoryID,
      c.Name AS CategoryName,
      COUNT(a.ArticleID) AS TotalArticles,
      SUM(LEN(a.Content) - LEN(REPLACE(a.Content, ' ', '')) + 1) AS TotalWords, -- Counting words in the article
      SUM(a.Views) AS TotalViews
  FROM 
      Articles a
  JOIN 
      Authors au ON a.AuthorID = au.AuthorID
  JOIN 
      Categories c ON a.CategoryID = c.CategoryID
  GROUP BY 
      a.AuthorID, au.Name, c.CategoryID, c.Name
  HAVING 
      SUM(a.Views) > 1000 -- Only include articles with more than 1000 views
      AND SUM(LEN(a.Content) - LEN(REPLACE(a.Content, ' ', '')) + 1) > 500 -- Only include articles with more than 500 words
  ORDER BY 
      TotalViews DESC;
  

  • The Articles table is joined with the Authors table on AuthorID and with the Categories table on CategoryID. These joins link the article to its author and category.
  • We GROUP BY a.AuthorID, au.Name, c.CategoryID, and c.Name to group articles by both their author and category.
  • We calculate the total number of articles (COUNT(a.ArticleID)), total number of words (SUM(LEN(a.Content) - LEN(REPLACE(a.Content, ' ', '')) + 1)), and total views (SUM(a.Views)).

Using GROUP BY, HAVING, and JOIN in SQL Server allows us to aggregate data from multiple tables and apply complex filtering and grouping logic. In this case, we filtered articles based on their word count and views, and displayed them in a structured way (grouped by author and category).

Hello Kiran,

In simple words, We need to understand the data and table structure in the query because the order of tables used in the joins is important. Follow the below two points

• Perform the necessary joins before applying the GROUP BY clause.
• This ensures all the columns needed for grouping or aggregation are available after the joins.

simple example of a SQL query using GROUP BY with joins:

SELECT 
    c.CustomerName, 
    COUNT(o.OrderID) AS OrderCount
FROM 
    Customers c
INNER JOIN 
    Orders o ON c.CustomerID = o.CustomerID
INNER JOIN 
    Products p ON o.ProductID = p.ProductID
GROUP BY 
    c.CustomerName;