Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
How To Apply Filtering Criteria at Group Level with The HAVING Clause in MS SQL Server?
Submitted by: AdministratorLet's say you have divided the query output into multiple groups with the GROUP BY clause. Now you are only interested in some of the groups, not all the groups. If you want to filter out some groups from the query, you can apply filtering criteria at the group level by using the HAVING clause inside the GROUP BY clause with this syntax:
SELECT group_level_fields FROM source_tables
WHERE search_condition
GROUP BY group_by_expression
HAVING group_filtering_condition
Since group_filtering_condition applies to groups, only group level expressions can be used in group_filtering_condition. The following tutorial exercise gives you some good examples of HAVING clause:
SELECT tag, COUNT(*), MIN(created), AVG(counts)
FROM ggl_links
GROUP BY tag HAVING AVG(counts) > 300
GO<pre>
tag COUNT(*) MIN(created) AVG(counts)
DBA 3 2005-01-01 774
SQA 2 2003-01-01 778</pre>
SELECT tag, COUNT(*), MIN(created), AVG(counts)
FROM ggl_links
GROUP BY tag
HAVING AVG(counts) > 300 AND tag = 'DBA'
GO<pre>
tag COUNT(*) MIN(created) AVG(counts)
DBA 3 2005-01-01 774</pre>
Note that the more criteria you have in the HAVING clause, the less groups you will get.
Submitted by: Administrator
SELECT group_level_fields FROM source_tables
WHERE search_condition
GROUP BY group_by_expression
HAVING group_filtering_condition
Since group_filtering_condition applies to groups, only group level expressions can be used in group_filtering_condition. The following tutorial exercise gives you some good examples of HAVING clause:
SELECT tag, COUNT(*), MIN(created), AVG(counts)
FROM ggl_links
GROUP BY tag HAVING AVG(counts) > 300
GO<pre>
tag COUNT(*) MIN(created) AVG(counts)
DBA 3 2005-01-01 774
SQA 2 2003-01-01 778</pre>
SELECT tag, COUNT(*), MIN(created), AVG(counts)
FROM ggl_links
GROUP BY tag
HAVING AVG(counts) > 300 AND tag = 'DBA'
GO<pre>
tag COUNT(*) MIN(created) AVG(counts)
DBA 3 2005-01-01 774</pre>
Note that the more criteria you have in the HAVING clause, the less groups you will get.
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.