{"id":12452,"date":"2018-04-09T06:07:05","date_gmt":"2018-04-09T06:07:05","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=12452"},"modified":"2021-05-09T13:11:03","modified_gmt":"2021-05-09T07:41:03","slug":"impala-having-clause","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/","title":{"rendered":"Impala HAVING Clause with Syntax & Restrictions"},"content":{"rendered":"

In our previous, tutorial we learn\u00a0Impala LIMIT Clause<\/strong>. Here, we are going to learn the Impala Having Clause. This is used to\u00a0specify conditions that filter which group results appear in the final results in\u00a0Impala<\/strong>. Moreover, we will see the introduction, syntax, type, and example, to understand it well.<\/span><\/p>\n

So, let’s discuss Impala Having Clause.<\/p>\n

Impala HAVING Clause<\/span><\/h2>\n

Basically, Impala HAVING Clause examines the results of aggregation functions rather than testing each individual table row then performs a filter operation on a SELECT query.<\/span><\/p>\n

Hence, we can say we use it in conjunction with functions always. Such as COUNT(), SUM(), AVG(), MIN(), or MAX(). Also, with the GROUP BY clause, \u00a0typically.<\/p>\n

In other words, it enables you to specify conditions that filter which group results appear in the final results in Impala.<\/span>
\n<\/span>
\nHowever, we use this clause along with the Group By Clause.<\/span> So, that places conditions on groups created by the GROUP BY Clause.
\n<\/span><\/p>\n

a. The syntax of HAVING Clause<\/span><\/h3>\n

So, the syntax for using Impala HAVING Clause is-<\/span>
\n<\/span><\/p>\n

select * from table_name ORDER BY col_name [ASC|DESC] [NULLS FIRST|NULLS LAST]<\/pre>\n
b. Restrictions in\u00a0Impala\u00a0Having Clause\u00a0<\/span><\/h3>\n
There is one restriction while using \u00a0Impala HAVING Clause is that here filter expression cannot include a scalar subquery.<\/span><\/p>\n
Example of Having Clause in Impala<\/span><\/h2>\n
For Example,<\/span>
\nLet us suppose we have a table named <\/span>Employees<\/b> in the database my_db. Its contents are \u2212<\/span>
\n<\/span><\/b><\/p>\n
[quickstart.cloudera:21000] > select * from Employees;<\/pre>\n
Query: select * from Employees <\/b><\/b><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
id<\/b><\/td>\nname<\/b><\/td>\nage<\/b><\/td>\naddress<\/b><\/td>\nsalary<\/b><\/td>\n<\/tr>\n
1<\/b><\/td>\nshubham<\/b><\/td>\n32<\/b><\/td>\ndelhi<\/b><\/td>\n20000<\/b><\/td>\n<\/tr>\n
2<\/b><\/td>\nmonika<\/b><\/td>\n25<\/b><\/td>\nmumbai<\/b><\/td>\n15000<\/b><\/td>\n<\/tr>\n
3<\/b><\/td>\nkajal<\/b><\/td>\n27<\/b><\/td>\nalirajpur<\/b><\/td>\n40000<\/b><\/td>\n<\/tr>\n
4<\/b><\/td>\nrevti<\/b><\/td>\n25<\/b><\/td>\nindore<\/b><\/td>\n35000<\/b><\/td>\n<\/tr>\n
5<\/b><\/td>\nshreyash<\/b><\/td>\n23<\/b><\/td>\npune<\/b><\/td>\n30000<\/b><\/td>\n<\/tr>\n
6<\/b><\/td>\nmehul<\/b><\/td>\n22<\/b><\/td>\nhyderabad<\/b><\/td>\n32000<\/b><\/td>\n<\/tr>\n
7 <\/b><\/td>\nVaishnavi<\/b><\/td>\n25<\/b><\/td>\nGoa<\/b><\/td>\n23000<\/b><\/td>\n<\/tr>\n
8<\/b><\/td>\nRishabh<\/b><\/td>\n22<\/b><\/td>\nchennai<\/b><\/td>\n31000<\/b><\/td>\n<\/tr>\n
9<\/b><\/td>\nPulkit<\/b><\/td>\n23<\/b><\/td>\nGandhi nagar<\/b><\/td>\n28000<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Fetched 9 row(s) in 0.51s<\/b>
\n<\/span>
\nSo, now let’s see an example of using Having clause in Impala \u2212<\/span>
\n<\/span><\/p>\n
[quickstart.cloudera:21000] > select max(salary) from Employees group by age having max(salary) > 20000;<\/pre>\n
This query displays those salaries, which are greater than 20000, but initially, it groups the table by age and selects the maximum salaries of each group.<\/span>
\n<\/span>20000 <\/b><\/p>\n\n\n\n\n\n\n\n
 max(salary)<\/b><\/td>\n<\/tr>\n
30000<\/b><\/td>\n<\/tr>\n
35000<\/b><\/td>\n<\/tr>\n
40000<\/b><\/td>\n<\/tr>\n
32000<\/b><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Fetched 4 row(s) in 1.30s<\/b>
\nSo, this was all about Impala Having Clause. Hope you like our explanation.<\/p>\n
Conclusion<\/span><\/h2>\n
Hence, we have seen the whole concept of Impala HAVING Clause. Moreover, we discussed syntax and example of Having Clause in Impala. Still, if any doubt occurs, feel free to ask in the comment section.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
In our previous, tutorial we learn\u00a0Impala LIMIT Clause. Here, we are going to learn the Impala Having Clause. This is used to\u00a0specify conditions that filter which group results appear in the final results in\u00a0Impala....<\/p>\n","protected":false},"author":6,"featured_media":19135,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[4283,5379,6529,11589,14050,15737],"class_list":["post-12452","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-impala","tag-example-of-impala-having-clause","tag-having-clause-in-impala","tag-impala-having-clause","tag-restrictions-of-impala-having-clause","tag-syntax-of-impala-having-clause","tag-what-is-having-clause-in-impala"],"yoast_head":"\nImpala HAVING Clause with Syntax & Restrictions - DataFlair<\/title>\n<meta name=\"description\" content=\"Impala HAVING Clause, What is Having Clause in impala, syntax of Impala having Clause, Example of Impala Having Clause, registration of Having Clause\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/impala-having-clause\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Impala HAVING Clause with Syntax & Restrictions - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Impala HAVING Clause, What is Having Clause in impala, syntax of Impala having Clause, Example of Impala Having Clause, registration of Having Clause\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/impala-having-clause\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-09T06:07:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-05-09T07:41:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-HAVING-Clause-01.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Impala HAVING Clause with Syntax & Restrictions - DataFlair","description":"Impala HAVING Clause, What is Having Clause in impala, syntax of Impala having Clause, Example of Impala Having Clause, registration of Having Clause","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/","og_locale":"en_US","og_type":"article","og_title":"Impala HAVING Clause with Syntax & Restrictions - DataFlair","og_description":"Impala HAVING Clause, What is Having Clause in impala, syntax of Impala having Clause, Example of Impala Having Clause, registration of Having Clause","og_url":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-04-09T06:07:05+00:00","article_modified_time":"2021-05-09T07:41:03+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-HAVING-Clause-01.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Impala HAVING Clause with Syntax & Restrictions","datePublished":"2018-04-09T06:07:05+00:00","dateModified":"2021-05-09T07:41:03+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/"},"wordCount":357,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-HAVING-Clause-01.jpg","keywords":["Example of Impala Having Clause","Having Clause in Impala","Impala HAVING Clause","Restrictions of Impala Having Clause","syntax of Impala having Clause","What is Having Clause in impala"],"articleSection":["Impala Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/impala-having-clause\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/","url":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/","name":"Impala HAVING Clause with Syntax & Restrictions - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-HAVING-Clause-01.jpg","datePublished":"2018-04-09T06:07:05+00:00","dateModified":"2021-05-09T07:41:03+00:00","description":"Impala HAVING Clause, What is Having Clause in impala, syntax of Impala having Clause, Example of Impala Having Clause, registration of Having Clause","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/impala-having-clause\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-HAVING-Clause-01.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Impala-HAVING-Clause-01.jpg","width":1200,"height":628,"caption":"Impala HAVING Clause with Syntax & Restrictions"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/impala-having-clause\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Impala Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/impala\/"},{"@type":"ListItem","position":3,"name":"Impala HAVING Clause with Syntax & Restrictions"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12452","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=12452"}],"version-history":[{"count":1,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12452\/revisions"}],"predecessor-version":[{"id":94040,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/12452\/revisions\/94040"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/19135"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=12452"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=12452"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=12452"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}