Section 05 Grouping Data/22 MySQL HAVING.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "611bc3ae-9afb-4d8a-a151-203015bf97b7",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL HAVING](https://www.mysqltutorial.org/mysql-having.aspx)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c237264d-eb93-4df6-995f-1a93489ce287",
   "metadata": {},
   "source": [
    "Use MySQL HAVING clause to specify a filter condition for groups of rows or aggregates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f7696366-15f5-4a6c-b141-1793156e861f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "205c3ec0-1203-4c02-92ec-0dbf08382ce0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d8a66cb-10cf-451e-a277-a129bb5511ef",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Introduction to MySQL HAVING clause"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efa04815-d129-4178-b816-9c32bcd6b2fd",
   "metadata": {},
   "source": [
    "syntax:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "6f57ef1e-abfa-4569-b9e8-2e5b890ca54f",
   "metadata": {},
   "source": [
    "SELECT \n",
    "    select_list\n",
    "FROM \n",
    "    table_name\n",
    "WHERE \n",
    "    search_condition\n",
    "GROUP BY \n",
    "    group_by_expression\n",
    "HAVING \n",
    "    group_condition;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a8650f95-28ae-4c52-ac4f-4faa29f001d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<svg xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" version=\"1.1\" width=\"757px\" height=\"52px\" viewBox=\"-0.5 -0.5 757 52\" content=\"&lt;mxfile host=&quot;Electron&quot; modified=&quot;2021-07-25T04:11:34.707Z&quot; agent=&quot;5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) draw.io/14.6.13 Chrome/89.0.4389.128 Electron/12.0.7 Safari/537.36&quot; etag=&quot;75Jd-Ho58keRNKzn_Hja&quot; version=&quot;14.6.13&quot; type=&quot;device&quot; pages=&quot;7&quot;&gt;&lt;diagram id=&quot;ssNlPmyW70ubCU7_ayOB&quot; name=&quot;Select&quot;&gt;3ZVNb6MwEIZ/DcdKGDcf14RAV9qmlUqlXe3NGyZA1jDIGULor18n2AHEKmpWVQ/lAn5mbGved2wc7ufHByXKdI0xSMdz46PDV47nMeZO9etEmpZMZ/MWJCqLW+R2IMrewMy0tMpi2BvWIkKUlJVDuMGigA0NmFAK62HaFmU8AKVIYASijZBj+iOLKW3pfOJ2/BtkSUqXgk0kFzbZgH0qYqx7iAcO9xUitV/50Qd5Es/qMnu6Z9P1+o2x1T0ufpfNa+rdtYuFt0y5lKCgoP9emkc7GS6f5uL7tt79WoaHnyy8s6VRY/WCWMtnhqgoxQQLIYOOLhVWRQynVV096nIeEUsNmYY7IGpML4iKUKOUcmmie1L4B3yUqM578tCdMG9yiViftMLLd5ZuJNpjpTZwpV7bgUIlQFfyvDbvJEavjYywD4A5kGp0ggIpKDsMe02Ylk0ueZ0t+sM4c4NLZt2DkJXZKXx5Xo+sGxpTpxlBVIqzHrU+3P8yoS/1yBbOF+5qoSPbTMq+XefnZnMOoAiOV+W0UW76srF3iRnX3QmeGZT2Dq891B9ugDcyIAoeA//161rgTdhnWaCH3SV6jvV+RTz4Cw==&lt;/diagram&gt;&lt;diagram id=&quot;BOAFXmV72-UQUAJ_qnbR&quot; name=&quot;Order By&quot;&gt;5ZZRb5swFIV/DY+TCA4kfWwIyR5aVUoqddubhy/gxnCRMSHk188JJoCYokZqp2rlBfzdaxOf4xNhET89rCXNk0dkICzHZgeLLC3HmUxsT99OpG6IN5s3IJacNcjuwJYfwcxsackZFIY1SCEKxfMhDDHLIFQDRqXEatgWoWADkNMYRmAbUjGmL5yppKFz1+74d+Bxoi4bNpWUts0GFAllWPUQCSziS0TVPKUHH8RJvFaXo1oX8ctu+gvd3e6Yw92z/eNbs9jqlimXLUjI1Psu7ZitqbrVC5iWzwxRqgRjzKgIOrqQWGYMTqvaetT1PCDmGk40fAWlanMWaKlQo0SlwlQLJXEHPgqU53eSle1OHPdSaX3SCi/euHUjUYGlDOFKHzEnkMoYrq3nNn0nMXrHyAi7BkxByVo3SBBU8f3wrFFzZONLX2eLfjDO3OCS+dV7KkrzptXm6XFk3dCYKuEKtjk961HpcP/NhL7UI1sIubeX97oScSH6dp2vm83Zg1RwuCqnqTpk1kyp2/8SE8GqS/DMoKQX3jbU727A9IvFxH1jTLxPFRN3FJNt8BD4z/9vUAiZf66geCMLnjbLYKPR4ueH28C8357rjW2IosgJww+0YUru/pUNeth9cZxrve82EvwB&lt;/diagram&gt;&lt;diagram id=&quot;v2iOQGe7vU604D6CA-oM&quot; name=&quot;Where&quot;&gt;5VdRb9owEP41PHYKcRLoI4TQVipjgk1dnyovNomL4ZhxSLJfPwc7CVkqBt06oY4XfJ/vbN99952UDvJX2Y3Am3gChPKObZGsg0Yd2+52LU/9FUiuEa/X10AkGNGQVQNz9oOayBJNGKFbg2lIAnDJNk0whPWahrKBYSEgbbotgJMGsMERbQHzEPM2+sCIjDXad60av6UsimWVsNlZ4dLZANsYE0gPIBR0kC8ApF6tMp/yonhlXboic3vxk9Uj4zT57vi76fT6Sh82PiekSkHQtXz10WS5ZMs++/jlWT71kT+5G3h3V2VqMi/rRYkqnzFByBgiWGMe1OhQQLImtDjVUlbtcw+wUWBXgc9Uytz0Ak4kKCiWK252t1LAkvrAQezvRGPL7dputVPypCo8PDF1U6ItJCKkR/ItOxCLiMojfkj7FcU4aCNT2BsKKypFrhwE5ViyXbPXsGnZqPKraVELw8wZLJlzd5gn5qbxbDppUdckJo2ZpPMN3tcjVeJ+iYTDUrdoQWhgjQZqZ8E4P6Rr/zubnB0VkmZHy2l2bftah+TlLDF9mtYK7hkoPhBvKeq/ToD9n8kEnSgT56JkgloyebgNZsGb64R43zzXa+tksVjYYfiGOkGOc1k6cVoMzIP7wP/8fkeV41mXRYF76aOKZkx+NXHF+rG49oNrrFFmXrE3cmOcx95vx5b34tg6YS46fzjeTOgnYCqPqodUqZo9ZLvNI/S7TNQv/VE94/Ut47VUO52NgpmCho/vV7luz/1XylVm/ZmgWas/tlDwEw==&lt;/diagram&gt;&lt;diagram id=&quot;T88nS42ogy0bWwMvv6_T&quot; name=&quot;Distinct&quot;&gt;5VhNc9owEP01PrZjWdghxwAmyTQkHWCapjfVFrapQIwQYPfXV0Yrf9QZCi1NPZThYL2VVtK+t+sFC/cX6a0gq3jEQ8osxw5TCw8sx7l2HCv/2mGmAddzNRCJJNSQXQKT5DvVIDLoJgnpGjANSc6ZTFZ1MODLJQ1kDSNC8F192oyzsAasSEQbwCQgrIk+J6GMNdp17RK/o0kUm52RDZYFMZMBWMck5LsKhH0L9wXnUj8t0j5leexMXBi5djvRGM9H9PHLh6z7aW5P32lnw1OWFFcQdCnP6xrIXcvMxIuGKnww5ELGPOJLwvwS7Qm+WYY092qrUTnngfOVApEC51TKDLRANpIrKJYLBta1FPwb7XPGxX5PPLRd5LiFxfCkItw78uoQojXfiIAemIdBgURE9JA/0HgejIqMILC3lC+oFJmaICgjMtnWtUZAslExr6RFPQAzJ7AEp94StoGdhuOnUYO6OjG7OJF0siL7eOxUbr9GQjXUDVowvrEHN8oySxir0rX/nEzOlgpJ04PhBKuDunoJ1BzkgYtdmcFXkJVxJXlNUp+dALdBwPOdP/YvlwGMnXYx0P13heogQ0XpomkiP8O6/Pkl3/a9sQxSOMV+kMHgNPZ+WbQQerVqHVEW3T+sbrD0I0/URUoRGdEYEXW9ugt9Llj1k0CKY/y+ZkwL0l7RnPntho5WCm7V+82cu1JfJ/6D359eboHtdLx2FVj0v7WC6NheELWrGUTNbnBwP5neP75BuoTeV8/1mukym82cIPiL6eJ6dsvSpdkSPo0H/lhBvZfLLVveVeeteFDD8re1bgnKPyiw/wM=&lt;/diagram&gt;&lt;diagram id=&quot;VV2ajXq8uar6lEuIePid&quot; name=&quot;Limit&quot;&gt;5VjbctowEP0aHtuxLGTCY8DmMgNNBjKT8KjYwlYjEBUymH59BZJvcYaBlgQP9QvesytZ2rNHXtyA3UXSF3gVjXlAWMO2gqQB3YZtA2A56meP7DTitO40EAoaaMjKgSn9TczIFI1pQNYG05DknEm6KoM+Xy6JL0sYFoJvy2FzzoISsMIhqQBTH7Mq+kwDGWn0Dlk5PiA0jGS2YeNZ4DTYAOsIB3xbgKDXgF3BudR3i6RL2D55aV5+xa+dfp/6b2zT83mCZ4PH9Tc9We+cIdkWBFnKy05tm63JXZovEqj0GZMLGfGQLzHzcrQjeLwMyH5WS1l5zIjzlQKBAn8SKXemFnAsuYIiuWDGu5aCv5EuZ1wcngl7FgI2yjwpTyrDnRO3blK05rHwyZE4aCoQi5Acmw/puH0yCmVkEtsnfEGk2KkAQRiWdFOuNWxKNsziclrUjWHmDJbMqjeYxeZJvcnDuEJdmZhtRCWZrvAhH1sl7o9IKKa6QguE95Z7rzxzyliRrsN1NjkbIiRJjqbTeFFLjzBHDmi1tb3NBdwyoowK2k01ffH8N+uuEpV/sXsxzzoYs73xHaWmmxSd7s5YF1bXx6o5QW7gX+Vlhj5yqjaS1ZHdhqVCsq13U+iFmVHviiRbxt/XDaro9nngTbzbFS5oOfVSbtqFXEO6RynKxHxhCYKTJQdr9YpL113QytQbed2n2xWL3UY1E8v/1g2CU9tBUK9+EFQbQnc4fRr++Eq5hAIHVPFQ8M0P1xWk1ATNmknpii3jdd476FQpOfWSUrVHe5i43kRBndntvnsQLHfG1xeMU+FhNBwPP/88C5xXBzlVCtRBZvv+J1LgIPurKFBm/plK/7nJP/ZB7w8=&lt;/diagram&gt;&lt;diagram id=&quot;sXheo244adPCd3jre5XT&quot; name=&quot;Group By&quot;&gt;5Vhdc6IwFP01PnaGED66j1XQ2tHawXY6fcxCBHaDsTGK7K/fKAkfy46rO1YZ5QVychOSe+7h5tKBvWQzYGgRjWmASUfXgk0HOh1dB0CzxG2LZDli2fc5ELI4yCGtBKbxLyxHKnQVB3gpsRzilBIeL+qgT+dz7PMahhijad1sRklQAxYoxA1g6iPSRN/jgEc5em9qJf6I4zDixYZlT4KUsQSWEQpoWoGg24E9RinPn5JND5Ot85RfePb59OF6n4mF9ScjvXOe3/S7fLL+MUOKLTA856edWpdb45nyFw6E+2STMh7RkM4RcUu0y+hqHuDtrJpolTYjShcCBAL8gTnPZCygFacCinhCZO+SM/oT9yihbPdO2NdMoJtFj+JJeLh74Nali5Z0xXy8xw7KCEQsxPvmM3O7rTMqYSQdO8A0wZxlwoBhgni8rscakiEbFnYlLeJBMnMES3LVa0RW8k19bzJuUFcnJo1ijqcLtPNHKsT9NxKqrm7QAuGD5jyInllMSJWu3XU0OWvMON7sdafsNaTiMvUp+Za301LAtjSJKtpVmj65/40bU4l5oErsVqnEbKjk/dH13OuVCTBgu3RiXU4nexkqlHNindgH6gSAUwtFDn2hsdhIERAQGvWAUG01Rb5SOeoPsotl/D//dkOBA2/y9iKg7seXyzCwvlum1ZThbDbTff8LZagbdrtkqI7eN6NDpa9/CxG2KmOpdVcEM3VHbu/1enMWNFt2tgO3VgKBQ2sg0K4iCDSrIGc4fR0+n1MuIUNBLHio5xdxXaJKMlt2/AMXrJMuk3cOLZSA1S4pNUuliee43nkOahfLPabZtoOa1eBhNBwPrzj9W9bZ0r9olv9m8wqn/MMN3d8=&lt;/diagram&gt;&lt;diagram id=&quot;1akUYF3EoQ8hf_R4YwGv&quot; name=&quot;Having&quot;&gt;7VnbdqIwFP0aH9sFhIs+WsXWmV5maTu9vMyKEAGLxImxQr9+ggn3GYfaWlnO8CJnJzlJzt6HQ7AFevPwnMCFe4Vt5LcUyQ5boN9SFFmWdPYTIxFHdKPNAYd4NoekDBh7r0iMTNCVZ6OlwDhEMfaptyiCFg4CZNECBgnB62K3KfbtArCADqoAYwv6VfTes6nL0bYmZfgF8hyXphsWLRNoPTsErwIxX4ADxFvmMHEjui5daON1DgJmC/QIxpTfzcMe8uOwJhG7s6In54vT9Qz9DoV0MXlWn064s8FbhqSbIyigO7sOXlf6bPazN5nIg9nX6Q836IUnydZolEQS2SywwsSEutjBAfTNDD3bRAvFXiVmZX0uMV4wUGbgDFEaCZXAFcUMcuncF61LSvAz6mEfk82cYCBpsqKlLQmDLMJnfH3xokpE/yUWot8Sr4iFtgQgESskDqJb+oGUcJZDCM8RJREbR5APqfdSXBwUYnbSfhkt7EYw8waWhN8X6K/ETIPRzVWFuiIxa9ejaLyAm+2vWdr/joR8qCu0ANCV+l3WMvV8P0/X5krJeUGEonAHeqrhFF40wN1EyUOmw+11ltqGUK6by+ok2z88/qAS//sLc2QeLwGyJjeLAa3CwEX3+/D6fO8U2PpE1/QqBdPpVLGsPVIAVKNZFOiHKxVbGUqLx75LhVGzVHQOWSqMSqKMzUuzd3u8zypVVZuVKO1//Z2qUzNR5D8Q+zmZ0qlkSn84vh1ef2auOATaHgt6sbCw6xAvXarSrDySD3g4aUbFkeueTmTlkJkkV88nN6O+OWLQ2ePxFh5dlRqWMEqFh8vh1fCIa78B2g2jQK1QcD66uft25KmgqJ2G8WA0vXawcJPoQcy1MR5j4zRt64f5xn6UWKFHH8R88X1uFLOyQbGRjNl3ndJqlinjnVVKDP2GPbbs7AAASgeAxE5c8PWLUfkvpGVH5TcgAE4NveiL77HiayPSdEvv0O0BDw81dbuD/nbV+t7fr9SawtX2IlwNGKed3FWqZdqOMgZayVE5H/asYUX5r+HP0zCoKWGu9Y/XsLpVw/qOGlbKH3U+TMPMzP6y492zv0SB+Qs=&lt;/diagram&gt;&lt;/mxfile&gt;\"><defs><linearGradient x1=\"0%\" y1=\"0%\" x2=\"0%\" y2=\"100%\" id=\"mx-gradient-ffffff-1-ffffff-1-s-0\"><stop offset=\"0%\" style=\"stop-color:#FFFFFF\"/><stop offset=\"100%\" style=\"stop-color:#ffffff\"/></linearGradient></defs><g><path d=\"M 71 26 L 91 26 L 79 26 L 90.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 96.76 26 L 88.76 30 L 90.76 26 L 88.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/><rect x=\"1\" y=\"1\" width=\"70\" height=\"50\" fill=\"#ffffff\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 2px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">FROM</div></div></div></foreignObject><text x=\"36\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">FROM</text></switch></g><rect x=\"99\" y=\"1\" width=\"70\" height=\"50\" fill=\"#ffffff\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 100px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">WHERE</div></div></div></foreignObject><text x=\"134\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">WHERE</text></switch></g><rect x=\"295\" y=\"1\" width=\"70\" height=\"50\" fill=\"#fff2cc\" stroke=\"#d6b656\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 296px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">HAVING</div></div></div></foreignObject><text x=\"330\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">HAVING</text></switch></g><path d=\"M 462 26 L 482 26 L 470 26 L 481.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 487.76 26 L 479.76 30 L 481.76 26 L 479.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/><rect x=\"392\" y=\"1\" width=\"70\" height=\"50\" fill=\"#ffffff\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 393px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">SELECT</div></div></div></foreignObject><text x=\"427\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">SELECT</text></switch></g><path d=\"M 560 26 L 580 26 L 568 26 L 579.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 585.76 26 L 577.76 30 L 579.76 26 L 577.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/><rect x=\"490\" y=\"1\" width=\"70\" height=\"50\" fill=\"url(#mx-gradient-ffffff-1-ffffff-1-s-0)\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 491px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">DISTINCT</div></div></div></foreignObject><text x=\"525\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">DISTINCT</text></switch></g><path d=\"M 658 26 L 678 26 L 666 26 L 677.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 683.76 26 L 675.76 30 L 677.76 26 L 675.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/><rect x=\"588\" y=\"1\" width=\"70\" height=\"50\" fill=\"#ffffff\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 589px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">ORDER BY</div></div></div></foreignObject><text x=\"623\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">ORDER BY</text></switch></g><rect x=\"686\" y=\"1\" width=\"70\" height=\"50\" fill=\"#ffffff\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 687px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">LIMIT</div></div></div></foreignObject><text x=\"721\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">LIMIT</text></switch></g><rect x=\"197\" y=\"1\" width=\"70\" height=\"50\" fill=\"#ffffff\" stroke=\"#33a0da\" stroke-width=\"2\" pointer-events=\"all\"/><g transform=\"translate(-0.5 -0.5)\"><switch><foreignObject style=\"overflow: visible; text-align: left;\" pointer-events=\"none\" width=\"100%\" height=\"100%\" requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"><div xmlns=\"http://www.w3.org/1999/xhtml\" style=\"display: flex; align-items: unsafe center; justify-content: unsafe center; width: 68px; height: 1px; padding-top: 26px; margin-left: 198px;\"><div style=\"box-sizing: border-box; font-size: 0; text-align: center; \"><div style=\"display: inline-block; font-size: 12px; font-family: Helvetica; color: #000000; line-height: 1.2; pointer-events: all; white-space: normal; word-wrap: normal; \">GROUP BY</div></div></div></foreignObject><text x=\"232\" y=\"30\" fill=\"#000000\" font-family=\"Helvetica\" font-size=\"12px\" text-anchor=\"middle\">GROUP BY</text></switch></g><path d=\"M 365 26 L 383.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 389.76 26 L 381.76 30 L 383.76 26 L 381.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/><path d=\"M 267 26 L 286.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 292.76 26 L 284.76 30 L 286.76 26 L 284.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/><path d=\"M 169 26 L 188.76 26\" fill=\"none\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"stroke\"/><path d=\"M 194.76 26 L 186.76 30 L 188.76 26 L 186.76 22 Z\" fill=\"#f05125\" stroke=\"#f05125\" stroke-width=\"2\" stroke-miterlimit=\"10\" pointer-events=\"all\"/></g><switch><g requiredFeatures=\"http://www.w3.org/TR/SVG11/feature#Extensibility\"/><a transform=\"translate(0,-5)\" xlink:href=\"https://www.diagrams.net/doc/faq/svg-export-text-problems\" target=\"_blank\"><text text-anchor=\"middle\" font-size=\"10px\" x=\"50%\" y=\"100%\">Viewer does not support full SVG 1.1</text></a></switch></svg>"
      ],
      "text/plain": [
       "<IPython.core.display.SVG object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SVG('../images/MySQL-Having.svg')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d56cb2fe-21b3-43ce-8cd7-f72a717d1cd9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL HAVING clause examples"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a75c9703-678b-43fd-a435-4aecd21b68a2",
   "metadata": {},
   "source": [
    "Find orders that have total amounts greater than 1000 and contain more than 600 items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "adca311b-c8fc-480b-b9b6-748621d2929a",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    orderNumber, \n",
    "    SUM(quantityOrdered) AS itemsCount,\n",
    "    SUM(quantityOrdered * priceEach) AS total\n",
    "FROM orderdetails\n",
    "GROUP BY orderNumber\n",
    "HAVING total > 1000 AND itemsCount > 600\n",
    "#LIMIT 5\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aa6a1d86-69e6-4e8b-ad27-e6e567387b2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('orderdetails', engine)\n",
    "    .assign(total = lambda df: df['quantityOrdered'] * df['priceEach'])\n",
    "    .groupby('orderNumber')\n",
    "    .agg(itemsCount = pd.NamedAgg(column='quantityOrdered', aggfunc='sum'),\n",
    "         total = pd.NamedAgg(column='total', aggfunc='sum'))\n",
    "    .query('total > 1000 and itemsCount > 600')\n",
    "    .reset_index()\n",
    "    [['orderNumber', 'itemsCount', 'total']]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f162bc7e-3b5e-4fda-a2eb-9a009f0eaaf2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "25a1a249-8fac-4645-86bd-31870f795459",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderNumber    0\n",
       "itemsCount     0\n",
       "total          2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "310c2826-51b0-4cc7-89bd-5f1b09a16735",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.4551915228366852e-11"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.total - df2.total).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f0559008-7531-423e-aa2b-83a881b2e43a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>itemsCount</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10106</td>\n",
       "      <td>675.0</td>\n",
       "      <td>52151.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10126</td>\n",
       "      <td>617.0</td>\n",
       "      <td>57131.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10135</td>\n",
       "      <td>607.0</td>\n",
       "      <td>55601.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10165</td>\n",
       "      <td>670.0</td>\n",
       "      <td>67392.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10168</td>\n",
       "      <td>642.0</td>\n",
       "      <td>50743.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber  itemsCount     total\n",
       "0        10106       675.0  52151.81\n",
       "1        10126       617.0  57131.92\n",
       "2        10135       607.0  55601.84\n",
       "3        10165       670.0  67392.85\n",
       "4        10168       642.0  50743.65"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcf6eae9-d75b-4b01-9010-69a09a9c4e39",
   "metadata": {},
   "source": [
    "Find all orders that already shipped and have a total amount greater than 1500"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1ebc5d88-59fc-4be6-b3b3-511d358eefa1",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    orderNumber,\n",
    "    status,\n",
    "    SUM(quantityOrdered*priceEach) AS total\n",
    "FROM orders\n",
    "INNER JOIN orderdetails USING (orderNumber)\n",
    "GROUP BY orderNumber, status\n",
    "HAVING status = \"Shipped\" AND total > 1500\n",
    ";'''\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fde03ba3-b1bd-42d5-8ae6-04233808f486",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('orders', engine)\n",
    "    .merge(pd.read_sql_table('orderdetails', engine),\n",
    "           on='orderNumber', how='inner')\n",
    "    .assign(total = lambda df: df['quantityOrdered'] * df['priceEach'])\n",
    "    .groupby(['orderNumber', 'status'])\n",
    "    .agg({'total': 'sum'})\n",
    "    .query('status == \"Shipped\" and total > 1500')\n",
    "    .reset_index()\n",
    "    [['orderNumber', 'status', 'total']]\n",
    ")\n",
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3ef71701-b7f9-4c09-9fd8-a1c6a7dea889",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderNumber     0\n",
       "status          0\n",
       "total          34\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0b970174-3e6e-4aa4-acbc-beffcadfd18f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.375610736431554e-10"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.total - df2.total).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b8a73b8d-d023-4328-ad7f-3f24c5147c19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>orderNumber</th>\n",
       "      <th>status</th>\n",
       "      <th>total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10100</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>10223.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10101</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>10549.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10102</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>5494.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10103</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>50218.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10104</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>40206.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orderNumber   status     total\n",
       "0        10100  Shipped  10223.83\n",
       "1        10101  Shipped  10549.01\n",
       "2        10102  Shipped   5494.78\n",
       "3        10103  Shipped  50218.95\n",
       "4        10104  Shipped  40206.20"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}