How can we implement Group By include having and where clause in SQL Server and which scenario these are required for Analytics reports
Loading
How can we implement Group By include having and where clause in SQL Server and which scenario these are required for Analytics reports
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Sreenath KappoorPosted Jan 14, 2025, 12:51 PM
Hi Kiran,
CREATE TABLE Articles (
ArticleID INT PRIMARY KEY,
AuthorID INT,
Category VARCHAR(50),
PublishedDate DATE,
Community VARCHAR(50)
)
INSERT INTO Articles (ArticleID, AuthorID, Category, PublishedDate, Community)
VALUES
(1, 101, 'Technology', '2024-02-01', 'c-sharpcorner'),
(2, 102, 'Health', '2024-03-10', 'geeks for geeks'),
(3, 103, 'Technology', '2024-02-15', 'c-sharpcorner'),
(4, 104, 'Lifestyle', '2024-05-21', 'geeks for geeks'),
(5, 105, 'Technology', '2023-12-30', 'c-sharpcorner'),
(6, 106, 'Health', '2024-07-15', 'geeks for geeks'),
(7, 107, 'Technology', '2024-09-01', 'c-sharpcorner'),
(8, 108, 'Health', '2024-10-20', 'Medium'),
(9, 109, 'Technology', '2024-11-05', 'c-sharpcorner'),
(10, 110, 'Lifestyle', '2024-01-10', 'Medium'),
(11, 111, 'Health', '2024-02-25', 'c-sharpcorner'),
(12, 112, 'Technology', '2024-04-18', 'Medium');
select * from Articles
I have a total of 12 articles: 6 on C# Corner, including 1 article in 2023, 3 on Geeks for Geeks, and 3 on Medium.
We can fetch data by filtering articles published in 2024, grouping them by community, and including only communities with more than 5 articles.
SELECT
Community,
COUNT(ArticleID) AS ArticleCount
FROM
Articles
WHERE
YEAR(PublishedDate) = 2024
GROUP BY
Community
HAVING
COUNT(ArticleID) > 3
We have one and only answer: c-sharpcorner.
Shubham SidnalePosted Jan 14, 2025, 5:17 PM
To use
GROUP BYwithWHEREandHAVINGin SQL Server:WHERE: Filters rows before grouping.GROUP BY: Groups data by specific columns.HAVING: Filters groups after aggregation.Example:
Key Points:
WHERE: Use to filter data before grouping (e.g., sales in January).GROUP BY: Use to group data (e.g., sales by category).HAVING: Use to filter groups after grouping (e.g., only categories with sales > $1,000).When to Use:
WHERE: Narrow down raw data (e.g., only this month’s sales).GROUP BY: Summarize data (e.g., total sales per category).HAVING: Focus on specific groups (e.g., high-performing categories).