Section 05 Grouping Data/23 MySQL ROLLUP.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cea72943-b7f5-4a17-b56f-9058858f968a",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL ROLLUP](https://www.mysqltutorial.org/mysql-rollup/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0546cd73-440c-4c69-b0fa-e680b28c7b73",
   "metadata": {},
   "source": [
    "Use the MySQL ROLLUP clause to generate subtotals and grand totals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0a0fc3fe-9953-43fc-9cde-f32637646ba0",
   "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": "2428e82a-a7b2-4db2-818a-3f6e14d5f829",
   "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": "faadd24c-a8a9-493a-8192-6e2abb8efba3",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Setting up a sample table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e30ad0f4-fa60-4ccc-9b68-f10d38f630ed",
   "metadata": {},
   "source": [
    "Creates a new table named `table` that stores the order values summarized by product lines and years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b94ac0ca-3654-4bcd-abad-3efa1a810fc8",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_19779/579765955.py:1: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  engine.execute('DROP TABLE IF EXISTS sales')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f3d1c4200a0>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS sales')\n",
    "\n",
    "string = '''\n",
    "CREATE TABLE sales\n",
    "SELECT\n",
    "    productLine,\n",
    "    YEAR(orderDate) orderYear,\n",
    "    SUM(quantityOrdered * priceEach) orderValue\n",
    "FROM orderdetails\n",
    "INNER JOIN orders USING (orderNumber)\n",
    "INNER JOIN products USING (productCode)\n",
    "GROUP BY productLine, YEAR(orderDate)\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cbc06600-6314-4835-995b-4af573abb689",
   "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>productLine</th>\n",
       "      <th>orderYear</th>\n",
       "      <th>orderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>2003</td>\n",
       "      <td>619161.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>2003</td>\n",
       "      <td>1374832.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>2003</td>\n",
       "      <td>376657.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trains</td>\n",
       "      <td>2003</td>\n",
       "      <td>65822.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ships</td>\n",
       "      <td>2003</td>\n",
       "      <td>222182.08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        productLine  orderYear  orderValue\n",
       "0      Vintage Cars       2003   619161.48\n",
       "1      Classic Cars       2003  1374832.22\n",
       "2  Trucks and Buses       2003   376657.12\n",
       "3            Trains       2003    65822.05\n",
       "4             Ships       2003   222182.08"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql('SELECT * FROM sales', engine).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "126699fd-909c-4403-89fe-cf2db4a522dc",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL ROLL UP Overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8ac1df50-b202-4b5d-a281-f6695ec0adbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a grouping set denoted by productLine\n",
    "string = '''\n",
    "SELECT productLine, SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    "GROUP BY productLine\n",
    "ORDER BY productLine ASC\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "60b6eac8-016b-431c-af74-fc92ccb80a59",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    .groupby('productLine', as_index=False)\n",
    "    .agg(totalOrderValue = pd.NamedAgg(\n",
    "        column='orderValue', aggfunc='sum'))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5149fd30-a378-4f5b-8682-0b4360ccb294",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a0407d5a-5f6f-4783-a46b-ab732d7b41c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "productLine        0\n",
       "totalOrderValue    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "845351cc-9d03-437c-ad45-00a6e9e1989c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.4924596548080444e-10"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.totalOrderValue - df2.totalOrderValue).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fc69328d-18e8-4c30-a6ff-c427aeaa1225",
   "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>productLine</th>\n",
       "      <th>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>3853922.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>1121426.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Planes</td>\n",
       "      <td>954637.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ships</td>\n",
       "      <td>663998.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Trains</td>\n",
       "      <td>188532.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>1024113.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1797559.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        productLine  totalOrderValue\n",
       "0      Classic Cars       3853922.49\n",
       "1       Motorcycles       1121426.12\n",
       "2            Planes        954637.54\n",
       "3             Ships        663998.34\n",
       "4            Trains        188532.92\n",
       "5  Trucks and Buses       1024113.57\n",
       "6      Vintage Cars       1797559.63"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d1c028be-78e0-41df-9765-32da304b6f51",
   "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>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9604190.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   totalOrderValue\n",
       "0       9604190.61"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create an empty grouping set\n",
    "string = '''\n",
    "SELECT\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM\n",
    "    sales\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "858a2408-4087-4e7d-8afb-c10d80c7887d",
   "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>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9604190.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   totalOrderValue\n",
       "0       9604190.61"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    [['orderValue']]\n",
    "    .sum()\n",
    "    .rename('totalOrderValue')\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dde97d0-fa1b-4933-9725-7bb2c74abde8",
   "metadata": {},
   "source": [
    "- Generate two or more grouping sets together in one query using UNION ALL clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4291fb0d-d2f3-4c88-97ca-b285225d1d01",
   "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>productLine</th>\n",
       "      <th>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1797559.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>3853922.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>1024113.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Trains</td>\n",
       "      <td>188532.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ships</td>\n",
       "      <td>663998.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Planes</td>\n",
       "      <td>954637.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>1121426.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>None</td>\n",
       "      <td>9604190.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        productLine  totalOrderValue\n",
       "0      Vintage Cars       1797559.63\n",
       "1      Classic Cars       3853922.49\n",
       "2  Trucks and Buses       1024113.57\n",
       "3            Trains        188532.92\n",
       "4             Ships        663998.34\n",
       "5            Planes        954637.54\n",
       "6       Motorcycles       1121426.12\n",
       "7              None       9604190.61"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    productLine,\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    "GROUP BY productLine\n",
    "UNION ALL\n",
    "SELECT\n",
    "    NULL, # UNION ALL requires all queries to have the same number of columns\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ef149d9-2a91-4d3b-ac49-ba72122d4d3b",
   "metadata": {},
   "source": [
    "- Generate two or more grouping sets together in one query using ROLLUP clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "30fec93f-1a10-4d98-8271-a72be1c48765",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT productLine, SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    "GROUP BY productLine WITH ROLLUP\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d6ca1271-f83a-46d4-bc2e-418baf9cd35c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    .groupby('productLine', as_index=False)\n",
    "    .agg(totalOrderValue=pd.NamedAgg(\n",
    "        column='orderValue', aggfunc='sum'))\n",
    ")\n",
    "\n",
    "agg_row = pd.DataFrame(\n",
    "    df2[['totalOrderValue']]\n",
    "    .sum()\n",
    ").T\n",
    "\n",
    "df2 = pd.concat([df2, agg_row], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9e7ae400-36a2-4e28-8ec6-8fd08ecfaece",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "571560b1-f822-458f-b56a-3af1a23ce355",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "productLine        1\n",
       "totalOrderValue    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "0abae93c-567e-456e-8406-11a171d366bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3.4924596548080444e-10"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.totalOrderValue - df2.totalOrderValue).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3b9c0283-ae9a-4af9-9e1a-2f9920e9857f",
   "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>productLine</th>\n",
       "      <th>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>3853922.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>1121426.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Planes</td>\n",
       "      <td>954637.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ships</td>\n",
       "      <td>663998.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Trains</td>\n",
       "      <td>188532.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>1024113.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>1797559.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>None</td>\n",
       "      <td>9604190.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        productLine  totalOrderValue\n",
       "0      Classic Cars       3853922.49\n",
       "1       Motorcycles       1121426.12\n",
       "2            Planes        954637.54\n",
       "3             Ships        663998.34\n",
       "4            Trains        188532.92\n",
       "5  Trucks and Buses       1024113.57\n",
       "6      Vintage Cars       1797559.63\n",
       "7              None       9604190.61"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36250ebe-da2d-4910-8d8a-cbb6e8d920c7",
   "metadata": {},
   "source": [
    "- Two columns specified in the `GROUP BY` clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "34fdb45d-487c-4a14-906e-b8ad8142a967",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', 50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "3ecabe60-45fd-46b5-9de8-1396a18c75b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    productLine,\n",
    "    orderYear,\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    "GROUP BY productLine, orderYear WITH ROLLUP\n",
    "ORDER BY\n",
    "    # https://stackoverflow.com/a/41237266/2757266\n",
    "    productLine IS NULL, # NULL last\n",
    "    productLine,\n",
    "    orderYear IS NULL,\n",
    "    orderYear\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "0af10227-d88e-43ef-a6eb-7431f3cdc01f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    .groupby(['productLine', 'orderYear'], as_index=False)\n",
    "    .agg(totalOrderValue=pd.NamedAgg(\n",
    "        column='orderValue', aggfunc='sum'))\n",
    ")\n",
    "\n",
    "agg_pl = (\n",
    "    df2\n",
    "    .groupby('productLine', as_index=False)\n",
    "    ['totalOrderValue']\n",
    "    .sum()\n",
    ")\n",
    "\n",
    "agg_all = pd.DataFrame(\n",
    "    df2\n",
    "    [['totalOrderValue']]\n",
    "    .sum()\n",
    ").T\n",
    "\n",
    "df2 = (pd.concat([df2, agg_pl, agg_all])\n",
    " .sort_values(['productLine', 'orderYear'], na_position='last')\n",
    " .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "3f277b39-08e0-4cd0-87e1-611d460d6246",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c0c60790-f2c8-4998-88a1-6e9c2aca9d90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "productLine        1\n",
       "orderYear          8\n",
       "totalOrderValue    3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d05c524d-a42a-4c47-b095-78f24719188c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "productLine        1\n",
       "orderYear          8\n",
       "totalOrderValue    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e92b3158-bc8e-402d-a2ce-b68ca5bb081a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.2118911147117615e-09"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.totalOrderValue - df2.totalOrderValue).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f349d4ea-49e4-4f3f-8855-49e299e3ae2d",
   "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>productLine</th>\n",
       "      <th>orderYear</th>\n",
       "      <th>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>1374832.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>1763136.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>715953.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3853922.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>348909.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>527243.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>245273.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1121426.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Planes</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>309784.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Planes</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>471971.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Planes</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>172881.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Planes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>954637.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Ships</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>222182.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Ships</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>337326.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Ships</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>104490.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Ships</td>\n",
       "      <td>NaN</td>\n",
       "      <td>663998.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Trains</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>65822.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Trains</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>96285.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Trains</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>26425.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Trains</td>\n",
       "      <td>NaN</td>\n",
       "      <td>188532.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>376657.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>465390.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>182066.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1024113.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>2003.0</td>\n",
       "      <td>619161.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>2004.0</td>\n",
       "      <td>854551.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>323846.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1797559.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9604190.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         productLine  orderYear  totalOrderValue\n",
       "0       Classic Cars     2003.0       1374832.22\n",
       "1       Classic Cars     2004.0       1763136.73\n",
       "2       Classic Cars     2005.0        715953.54\n",
       "3       Classic Cars        NaN       3853922.49\n",
       "4        Motorcycles     2003.0        348909.24\n",
       "5        Motorcycles     2004.0        527243.84\n",
       "6        Motorcycles     2005.0        245273.04\n",
       "7        Motorcycles        NaN       1121426.12\n",
       "8             Planes     2003.0        309784.20\n",
       "9             Planes     2004.0        471971.46\n",
       "10            Planes     2005.0        172881.88\n",
       "11            Planes        NaN        954637.54\n",
       "12             Ships     2003.0        222182.08\n",
       "13             Ships     2004.0        337326.10\n",
       "14             Ships     2005.0        104490.16\n",
       "15             Ships        NaN        663998.34\n",
       "16            Trains     2003.0         65822.05\n",
       "17            Trains     2004.0         96285.53\n",
       "18            Trains     2005.0         26425.34\n",
       "19            Trains        NaN        188532.92\n",
       "20  Trucks and Buses     2003.0        376657.12\n",
       "21  Trucks and Buses     2004.0        465390.00\n",
       "22  Trucks and Buses     2005.0        182066.45\n",
       "23  Trucks and Buses        NaN       1024113.57\n",
       "24      Vintage Cars     2003.0        619161.48\n",
       "25      Vintage Cars     2004.0        854551.85\n",
       "26      Vintage Cars     2005.0        323846.30\n",
       "27      Vintage Cars        NaN       1797559.63\n",
       "28              None        NaN       9604190.61"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbd47509-6ad4-431f-a935-39c55ade2893",
   "metadata": {},
   "source": [
    "Reverse the herarchy:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f570626b-09f8-4462-b7f1-e5acb35f9aab",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "SELECT\n",
    "    orderYear,\n",
    "    productLine,\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    "GROUP BY orderYear, productLine\n",
    "WITH ROLLUP\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "db036267-7948-41ca-a554-f419c4fbbf9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    .groupby(['orderYear', 'productLine'], as_index=False)\n",
    "    .agg(totalOrderValue = pd.NamedAgg(\n",
    "        column='orderValue', aggfunc='sum'))\n",
    "    [['orderYear', 'productLine', 'totalOrderValue']]\n",
    "    .sort_values(by=['orderYear', 'productLine'])\n",
    ")\n",
    "\n",
    "agg_year = (\n",
    "    df2\n",
    "    .groupby('orderYear', as_index=False)\n",
    "    .agg({'totalOrderValue': 'sum'})\n",
    "    [['orderYear', 'totalOrderValue']]\n",
    ")\n",
    "agg_all = pd.DataFrame(\n",
    "    agg_year[['totalOrderValue']].sum()\n",
    ").T\n",
    "\n",
    "\n",
    "df2 = (\n",
    "    pd.concat([df2, agg_year, agg_all], axis=0)\n",
    "    .sort_values(['orderYear', 'productLine'])\n",
    "    .reset_index(drop=True)   \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c45f5b9e-da60-43b1-960c-b3bdc95c856c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderYear          1\n",
       "productLine        4\n",
       "totalOrderValue    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "359c82f5-894e-4e94-86d8-90101ff9d38c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderYear          1\n",
       "productLine        4\n",
       "totalOrderValue    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "605dce80-1ffe-476e-bd0d-fa253814a6cb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.656612873077393e-10"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.totalOrderValue - df2.totalOrderValue).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "68d6cedc-7de8-4c04-9d41-8fc0dfe88ba3",
   "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>orderYear</th>\n",
       "      <th>productLine</th>\n",
       "      <th>totalOrderValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1374832.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>348909.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Planes</td>\n",
       "      <td>309784.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Ships</td>\n",
       "      <td>222182.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Trains</td>\n",
       "      <td>65822.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>376657.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>619161.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>None</td>\n",
       "      <td>3317348.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1763136.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>527243.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Planes</td>\n",
       "      <td>471971.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Ships</td>\n",
       "      <td>337326.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Trains</td>\n",
       "      <td>96285.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>465390.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>854551.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>None</td>\n",
       "      <td>4515905.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>715953.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>245273.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Planes</td>\n",
       "      <td>172881.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Ships</td>\n",
       "      <td>104490.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Trains</td>\n",
       "      <td>26425.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>182066.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>323846.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1770936.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>9604190.61</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    orderYear       productLine  totalOrderValue\n",
       "0      2003.0      Classic Cars       1374832.22\n",
       "1      2003.0       Motorcycles        348909.24\n",
       "2      2003.0            Planes        309784.20\n",
       "3      2003.0             Ships        222182.08\n",
       "4      2003.0            Trains         65822.05\n",
       "5      2003.0  Trucks and Buses        376657.12\n",
       "6      2003.0      Vintage Cars        619161.48\n",
       "7      2003.0              None       3317348.39\n",
       "8      2004.0      Classic Cars       1763136.73\n",
       "9      2004.0       Motorcycles        527243.84\n",
       "10     2004.0            Planes        471971.46\n",
       "11     2004.0             Ships        337326.10\n",
       "12     2004.0            Trains         96285.53\n",
       "13     2004.0  Trucks and Buses        465390.00\n",
       "14     2004.0      Vintage Cars        854551.85\n",
       "15     2004.0              None       4515905.51\n",
       "16     2005.0      Classic Cars        715953.54\n",
       "17     2005.0       Motorcycles        245273.04\n",
       "18     2005.0            Planes        172881.88\n",
       "19     2005.0             Ships        104490.16\n",
       "20     2005.0            Trains         26425.34\n",
       "21     2005.0  Trucks and Buses        182066.45\n",
       "22     2005.0      Vintage Cars        323846.30\n",
       "23     2005.0              None       1770936.71\n",
       "24        NaN              None       9604190.61"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75f3a144-acf5-4063-894a-63908461ec84",
   "metadata": {
    "tags": []
   },
   "source": [
    "### The GROUPING() function"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b2e022a-8d6f-4b3f-b255-4ac58ef6cc71",
   "metadata": {},
   "source": [
    "To check whether `NULL` in the result set represents the subtotals or grandtotals."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "9377848c-0800-4706-8bb1-70e5eae4ef01",
   "metadata": {},
   "outputs": [],
   "source": [
    "# GROUPING(orderYear) returns 1 when NULL in the orderYear, 0 otherwise\n",
    "string = '''\n",
    "SELECT\n",
    "    orderYear,\n",
    "    productLine,\n",
    "    SUM(orderValue) totalOrderValue,\n",
    "    GROUPING(orderYear) AS GROUPING_orderYear,\n",
    "    GROUPING(productLine) AS GROUPING_productLine\n",
    "FROM sales\n",
    "GROUP BY orderYear, productLine WITH ROLLUP\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql_query(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a27a329b-26ba-4660-a71b-fb1ade96ce3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    .groupby(['orderYear', 'productLine'], as_index=False)\n",
    "    .agg(totalOrderValue = pd.NamedAgg(\n",
    "        column='orderValue', aggfunc='sum'))\n",
    "    [['orderYear', 'productLine', 'totalOrderValue']]\n",
    ")\n",
    "\n",
    "agg_pl = (\n",
    "    df2\n",
    "    .groupby('orderYear', as_index=False)\n",
    "    [['totalOrderValue']]\n",
    "    .sum()\n",
    ")\n",
    "\n",
    "agg_all = pd.DataFrame(\n",
    "    agg_pl[['totalOrderValue']].sum()\n",
    ").T\n",
    "\n",
    "df2 = (\n",
    "    pd.concat([df2, agg_pl, agg_all], axis=0)\n",
    "    .assign(GROUPING_orderYear = lambda df: df.orderYear.isna().astype(int),\n",
    "            GROUPING_productLine = lambda df: df.productLine.isna().astype(int))\n",
    "    .sort_values(['orderYear', 'productLine'], ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f03d426f-4ed1-471f-b89d-bc8040e1dff0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "892166d5-6049-42cb-bdbd-6d4556528f24",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderYear               1\n",
       "productLine             4\n",
       "totalOrderValue         1\n",
       "GROUPING_orderYear      0\n",
       "GROUPING_productLine    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "dba22d7d-56c7-4724-b192-ee64586a1f34",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderYear               1\n",
       "productLine             4\n",
       "totalOrderValue         0\n",
       "GROUPING_orderYear      0\n",
       "GROUPING_productLine    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "36af7df6-1151-4f57-ae83-ddd39a83ad7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.656612873077393e-10"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.totalOrderValue - df2.totalOrderValue).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "60d2c545-6801-4424-b7a3-69242846f722",
   "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>orderYear</th>\n",
       "      <th>productLine</th>\n",
       "      <th>totalOrderValue</th>\n",
       "      <th>GROUPING_orderYear</th>\n",
       "      <th>GROUPING_productLine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1374832.22</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>348909.24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Planes</td>\n",
       "      <td>309784.20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Ships</td>\n",
       "      <td>222182.08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Trains</td>\n",
       "      <td>65822.05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>376657.12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>619161.48</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2003.0</td>\n",
       "      <td>None</td>\n",
       "      <td>3317348.39</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>1763136.73</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>527243.84</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Planes</td>\n",
       "      <td>471971.46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Ships</td>\n",
       "      <td>337326.10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Trains</td>\n",
       "      <td>96285.53</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>465390.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>854551.85</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2004.0</td>\n",
       "      <td>None</td>\n",
       "      <td>4515905.51</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Classic Cars</td>\n",
       "      <td>715953.54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Motorcycles</td>\n",
       "      <td>245273.04</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Planes</td>\n",
       "      <td>172881.88</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Ships</td>\n",
       "      <td>104490.16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Trains</td>\n",
       "      <td>26425.34</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Trucks and Buses</td>\n",
       "      <td>182066.45</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>Vintage Cars</td>\n",
       "      <td>323846.30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2005.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1770936.71</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>9604190.61</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    orderYear       productLine  totalOrderValue  \\\n",
       "0      2003.0      Classic Cars       1374832.22   \n",
       "1      2003.0       Motorcycles        348909.24   \n",
       "2      2003.0            Planes        309784.20   \n",
       "3      2003.0             Ships        222182.08   \n",
       "4      2003.0            Trains         65822.05   \n",
       "5      2003.0  Trucks and Buses        376657.12   \n",
       "6      2003.0      Vintage Cars        619161.48   \n",
       "7      2003.0              None       3317348.39   \n",
       "8      2004.0      Classic Cars       1763136.73   \n",
       "9      2004.0       Motorcycles        527243.84   \n",
       "10     2004.0            Planes        471971.46   \n",
       "11     2004.0             Ships        337326.10   \n",
       "12     2004.0            Trains         96285.53   \n",
       "13     2004.0  Trucks and Buses        465390.00   \n",
       "14     2004.0      Vintage Cars        854551.85   \n",
       "15     2004.0              None       4515905.51   \n",
       "16     2005.0      Classic Cars        715953.54   \n",
       "17     2005.0       Motorcycles        245273.04   \n",
       "18     2005.0            Planes        172881.88   \n",
       "19     2005.0             Ships        104490.16   \n",
       "20     2005.0            Trains         26425.34   \n",
       "21     2005.0  Trucks and Buses        182066.45   \n",
       "22     2005.0      Vintage Cars        323846.30   \n",
       "23     2005.0              None       1770936.71   \n",
       "24        NaN              None       9604190.61   \n",
       "\n",
       "    GROUPING_orderYear  GROUPING_productLine  \n",
       "0                    0                     0  \n",
       "1                    0                     0  \n",
       "2                    0                     0  \n",
       "3                    0                     0  \n",
       "4                    0                     0  \n",
       "5                    0                     0  \n",
       "6                    0                     0  \n",
       "7                    0                     1  \n",
       "8                    0                     0  \n",
       "9                    0                     0  \n",
       "10                   0                     0  \n",
       "11                   0                     0  \n",
       "12                   0                     0  \n",
       "13                   0                     0  \n",
       "14                   0                     0  \n",
       "15                   0                     1  \n",
       "16                   0                     0  \n",
       "17                   0                     0  \n",
       "18                   0                     0  \n",
       "19                   0                     0  \n",
       "20                   0                     0  \n",
       "21                   0                     0  \n",
       "22                   0                     0  \n",
       "23                   0                     1  \n",
       "24                   1                     1  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27e5b2a0-edcf-4ba8-83e4-eca8c354c147",
   "metadata": {
    "tags": []
   },
   "source": [
    "### combine IF() function with GROUPING() function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5b0977fc-4cea-4de0-8fe0-1262dc6de4ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# substitute labels for the super-aggregate NULL values\n",
    "# in orderYear and productLine columns\n",
    "string = '''\n",
    "SELECT\n",
    "    IF(GROUPING(orderYear), 'ALL Years', orderYear) orderYear,\n",
    "    IF(GROUPING(productLine), 'ALL Product Lines', productLine) productLine,\n",
    "    SUM(orderValue) totalOrderValue\n",
    "FROM sales\n",
    "GROUP BY orderYear, productLine WITH ROLLUP\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "99408e16-4103-4c28-93e8-b5b65d2333a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table('sales', engine)\n",
    "    .groupby(['orderYear', 'productLine'], as_index=False)\n",
    "    .agg(totalOrderValue = pd.NamedAgg(\n",
    "        column='orderValue', aggfunc='sum'))\n",
    "    [['orderYear', 'productLine', 'totalOrderValue']]\n",
    ")\n",
    "\n",
    "agg_year = (\n",
    "    df2\n",
    "    .groupby('orderYear', as_index=False)\n",
    "    .agg({'totalOrderValue': 'sum'})\n",
    "    .assign(productLine = 'ALL Product Lines')\n",
    ")\n",
    "\n",
    "agg_all = pd.DataFrame(\n",
    "    agg_year[['totalOrderValue']].sum()\n",
    ").T\n",
    "\n",
    "agg_all = agg_all.assign(\n",
    "    productLine = 'ALL Product Lines',\n",
    "    orderYear = 'ALL Years')\n",
    "\n",
    "df2 = pd.concat([df2, agg_year, agg_all], axis=0)\n",
    "\n",
    "categories = ['Classic Cars', 'Motorcycles', 'Planes', 'Ships', 'Trains',\n",
    "              'Trucks and Buses', 'Vintage Cars', 'ALL Product Lines']\n",
    "df2 = (\n",
    "    df2\n",
    "    .assign(productLine = pd.Categorical(\n",
    "        df2.productLine, categories=categories, ordered=True),\n",
    "            orderYear = df2.orderYear.astype(str))\n",
    "    .sort_values(['orderYear', 'productLine'], ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "538470f8-4dd9-4be5-a320-14ff355fb5f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = (\n",
    "    df1\n",
    "    .assign(productLine = pd.Categorical(\n",
    "        df1.productLine, categories=categories, ordered=True),\n",
    "            orderYear = df1.orderYear.astype(str))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "e2e02756-2694-4a12-9a2f-5316dbc9ac53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "b6bed2df-1a13-4338-9a16-7be05c75ef5c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "orderYear          0\n",
       "productLine        0\n",
       "totalOrderValue    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1 != df2).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "200dda43-5142-45a5-8356-b2d878f4045e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.656612873077393e-10"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df1.totalOrderValue - df2.totalOrderValue).abs().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "b65851a2-5b3e-42d8-9887-0468b32fd344",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f3ca79f59a0>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine.execute('DROP TABLE IF EXISTS sales;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "8816f346-b3dc-4abe-b805-7675b72c7a86",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', 10) "
   ]
  }
 ],
 "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
}