{"id":6913,"date":"2017-08-27T12:49:18","date_gmt":"2017-08-27T10:49:18","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=6913"},"modified":"2017-08-27T12:49:18","modified_gmt":"2017-08-27T10:49:18","slug":"sql-les-requetes-regroupement","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=6913","title":{"rendered":"SQL : les requ\u00eates regroupement"},"content":{"rendered":"<p style=\"text-align: justify;\">Les requ\u00eates \u00ab regroupement \u00bb permettent de regrouper tous les enregistrements (lignes) d\u2019une m\u00eame table (ou d&#8217;une requ\u00eate), dont le contenu de certains champs est commun, en un seul enregistrement.<\/p>\n<p style=\"text-align: justify;\">Ainsi,\u00a0dans le cas d&#8217;un\u00a0\u00e9tat de stock de marchandises, la requ\u00eate ci-apr\u00e8s regroupe tous les\u00a0enregistrements dont le code article\u00a0(champ R\u00e9f\u00e9rence) est identique\u00a0en un seul et additionne les quantit\u00e9s :<\/p>\n<pre class=\"lang:default decode:true\">SELECT [Stock N].R\u00e9f\u00e9rence, [Stock N].D\u00e9signation, Sum([Stock N].Quantit\u00e9) AS SommeDeQuantit\u00e9\r\nFROM [Stock N]\r\nGROUP BY [Stock N].R\u00e9f\u00e9rence, [Stock N].D\u00e9signation;<\/pre>\n<p style=\"text-align: justify;\">Les champs qui servent de crit\u00e8re de regroupement sont mentionn\u00e9s dans la clause <em>GROUP BY<\/em> (dans notre exemple, le regroupement est op\u00e9r\u00e9 sur les champs R\u00e9f\u00e9rences et D\u00e9signation). Les champs qui ne servent pas de crit\u00e8re de regroupement font l\u2019objet d\u2019un calcul \u00e0 l&#8217;aide d&#8217;une fonction d&#8217;agr\u00e9gation :<\/p>\n<ul>\n<li style=\"text-align: justify;\">d\u2019une addition (<em>SUM()<\/em>) comme dans l\u2019exemple propos\u00e9,<\/li>\n<li style=\"text-align: justify;\">d\u2019une moyenne arithm\u00e9tique (<em>AVG()<\/em>),<\/li>\n<li style=\"text-align: justify;\">d\u2019un d\u00e9nombrement (<em>COUNT()<\/em>),<\/li>\n<li style=\"text-align: justify;\">d&#8217;un relev\u00e9 des valeurs minimales (<em>MIN()<\/em>) et\u00a0maximales (<em>MAX()<\/em>)\u2026<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Dans cet exemple, le champ Quantit\u00e9 est somm\u00e9.<\/p>\n<p>Table d&#8217;origine :<\/p>\n<table style=\"width: 548.7px;\">\n<tbody>\n<tr>\n<td style=\"width: 70px;\">R\u00e9f\u00e9rence<\/td>\n<td style=\"width: 320px;\">D\u00e9signation<\/td>\n<td style=\"width: 149.7px;\">Quantit\u00e9<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">150<\/td>\n<td style=\"width: 320px;\">PALETTE<\/td>\n<td style=\"width: 149.7px;\">181<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\"><span style=\"color: #ff9900;\">300<\/span><\/td>\n<td style=\"width: 320px;\"><span style=\"color: #ff9900;\">CHEVRON 6X8<\/span><\/td>\n<td style=\"width: 149.7px;\"><span style=\"color: #ff9900;\">1400<\/span><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\"><span style=\"color: #ff9900;\">300<\/span><\/td>\n<td style=\"width: 320px;\"><span style=\"color: #ff9900;\">CHEVROB 6X8<\/span><\/td>\n<td style=\"width: 149.7px;\"><span style=\"color: #ff9900;\">208<\/span><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">512<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 12 MM(F.UT: 500KG)<\/td>\n<td style=\"width: 149.7px;\">12<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">516<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 16 MM(F.UT:1000KG)<\/td>\n<td style=\"width: 149.7px;\">48<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">520<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 20 MM(F.UT:1600KG)<\/td>\n<td style=\"width: 149.7px;\">64<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">524<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 24 MM<\/td>\n<td style=\"width: 149.7px;\">15<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">R\u00e9sultat de la requ\u00eate :<\/p>\n<table style=\"width: 548.7px;\">\n<tbody>\n<tr>\n<td style=\"width: 70px;\">R\u00e9f\u00e9rence<\/td>\n<td style=\"width: 320px;\">D\u00e9signation<\/td>\n<td style=\"width: 149.7px;\">SommeDeQuantit\u00e9<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">150<\/td>\n<td style=\"width: 320px;\">PALETTE<\/td>\n<td style=\"width: 149.7px;\">181<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\"><span style=\"color: #ff9900;\">300<\/span><\/td>\n<td style=\"width: 320px;\"><span style=\"color: #ff9900;\">CHEVRON 6X8<\/span><\/td>\n<td style=\"width: 149.7px;\"><span style=\"color: #ff9900;\">1608<\/span><\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">512<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 12 MM(F.UT: 500KG)<\/td>\n<td style=\"width: 149.7px;\">12<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">516<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 16 MM(F.UT:1000KG)<\/td>\n<td style=\"width: 149.7px;\">48<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">520<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 20 MM(F.UT:1600KG)<\/td>\n<td style=\"width: 149.7px;\">64<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\">524<\/td>\n<td style=\"width: 320px;\">ELINGUES DIAM 24 MM<\/td>\n<td style=\"width: 149.7px;\">15<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">Une clause <em>HAVING<\/em> positionn\u00e9e \u00e0 la fin de la requ\u00eate permettra d\u2019ajouter un crit\u00e8re (\u00e0 l&#8217;aide <a href=\"https:\/\/www.auditsi.eu\/?p=6979\">d&#8217;op\u00e9rateurs de comparaison<\/a>) afin de restreindre le nombre de lignes compris dans le r\u00e9sultat de la requ\u00eate. Par exemple, pour ne retenir que les r\u00e9sultats dont la quantit\u00e9 est sup\u00e9rieure \u00e0 1 000 :<\/p>\n<p style=\"text-align: center;\">HAVING (((Sum([Stock N].Quantit\u00e9))&gt;1000))<\/p>\n<p>Le texte de la requ\u00eate incluant la clause <em>HAVING<\/em> prend la forme suivante :<\/p>\n<pre class=\"lang:default decode:true\">SELECT [Stock N].R\u00e9f\u00e9rence, [Stock N].D\u00e9signation, Sum([Stock N].Quantit\u00e9) AS SommeDeQuantit\u00e9\r\nFROM [Stock N]\r\nGROUP BY [Stock N].R\u00e9f\u00e9rence, [Stock N].D\u00e9signation\r\nHAVING (((Sum([Stock N].Quantit\u00e9))&gt;1000));\r\n<\/pre>\n<p>R\u00e9sultat de la requ\u00eate avec la clause <em>HAVING<\/em> :<\/p>\n<table style=\"width: 548.7px;\">\n<tbody>\n<tr>\n<td style=\"width: 70px;\">R\u00e9f\u00e9rence<\/td>\n<td style=\"width: 320px;\">D\u00e9signation<\/td>\n<td style=\"width: 149.7px;\">SommeDeQuantit\u00e9<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 70px;\"><span style=\"color: #ff9900;\">300<\/span><\/td>\n<td style=\"width: 320px;\"><span style=\"color: #ff9900;\">CHEVRON 6X8<\/span><\/td>\n<td style=\"width: 149.7px;\"><span style=\"color: #ff9900;\">1608<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p style=\"text-align: justify;\">Une requ\u00eate \u00ab regroupement \u00bb sera utile\u00a0par exemple pour obtenir une balance \u00e0 partir des journaux d&#8217;\u00e9critures comptables.<\/p>\n<p>Le r\u00e9sultat de la requ\u00eate peut faire l&#8217;objet d&#8217;un tri avec une clause <a href=\"https:\/\/www.auditsi.eu\/?p=6899\"><em>ORDER BY<\/em><\/a>.<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?cat=1820\">Ma\u00eetriser le langage de requ\u00eate SQL<\/a>\u00a0\/ <a href=\"https:\/\/www.auditsi.eu\/?cat=50\">Analyse de donn\u00e9es<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"SQL%20%3A%20les%20requ%C3%AAtes%20regroupement\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Les requ\u00eates \u00ab regroupement \u00bb permettent de regrouper tous les enregistrements (lignes) d\u2019une m\u00eame table (ou d&#8217;une requ\u00eate), dont le contenu de certains champs est commun, en un seul enregistrement. Ainsi,\u00a0dans le cas d&#8217;un\u00a0\u00e9tat de stock de marchandises, la requ\u00eate ci-apr\u00e8s regroupe tous les\u00a0enregistrements dont le code article\u00a0(champ R\u00e9f\u00e9rence) est identique\u00a0en un seul et additionne &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=6913\" class=\"more-link\">Continue reading &lsquo;SQL : les requ\u00eates regroupement&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"SQL%20%3A%20les%20requ%C3%AAtes%20regroupement\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[50,1820,282],"tags":[1694,377,166,1860,265,1140,290,1861,1751,1750,1862,1142,57,1864,288],"class_list":["post-6913","post","type-post","status-publish","format-standard","hentry","category-audit-de-donnees","category-requetes-sql","category-sql","tag-addition","tag-agregation-de-donnees","tag-analyse-de-donnees","tag-avg","tag-count","tag-denombrement","tag-group-by","tag-having","tag-max","tag-min","tag-moyenne-arithmetique","tag-operateurs-de-comparaison","tag-requete-sql","tag-select-from-where-group-by-having-order-by","tag-sum"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6913","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6913"}],"version-history":[{"count":13,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6913\/revisions"}],"predecessor-version":[{"id":7164,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6913\/revisions\/7164"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6913"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6913"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6913"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}