{"id":6678,"date":"2020-07-01T11:52:36","date_gmt":"2020-07-01T16:52:36","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=6678"},"modified":"2020-07-01T11:52:36","modified_gmt":"2020-07-01T16:52:36","slug":"oracle-group-by-having","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=6678","title":{"rendered":"Oracle &#8211; Group By Having"},"content":{"rendered":"<p>I needed a query to find records where duplicate name values exist. I know how to group by and count, but the table has millions of records. I don&#8217;t <em>want<\/em> the 99% of the data where no duplication occurs. By using &#8220;having&#8221; in conjunction with &#8220;group by&#8221;, I am able to restrict the output to the groups that match my criterion.<\/p>\n<pre>select display_name, count(display_name) from circuit\r\ngroup by display_name\r\nhaving count(display_name) &gt; 1;<\/pre>\n<p>My result set is the display name &amp; occurrence count for that display name <em>without<\/em> all of the &#8216;good&#8217; records where there&#8217;s a unique display name. (Yes, I know uniqueness could be enforced. The real scenario isn&#8217;t this straight-forward. There <em>are<\/em> times where the display name should be the same and I&#8217;ve got additional filters that drop out those cases).<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I needed a query to find records where duplicate name values exist. I know how to group by and count, but the table has millions of records. I don&#8217;t want the 99% of the data where no duplication occurs. By using &#8220;having&#8221; in conjunction with &#8220;group by&#8221;, I am able to restrict the output to &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1004],"tags":[595,922],"class_list":["post-6678","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/6678","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6678"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/6678\/revisions"}],"predecessor-version":[{"id":6679,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/6678\/revisions\/6679"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6678"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6678"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6678"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}