ex07-Aggregating data with GROUP BY and ORDER BY.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ex07-Aggregating data with GROUP BY and ORDER BY\n",
    "\n",
    "The ***GROUP BY*** clause is an optional clause of the SELECT statement. The GROUP BY clause a selected group of rows into summary rows by values of one or more columns. \n",
    "\n",
    "The GROUP BY clause returns one row for each group. For each group, you can apply an aggregate function such as ***MIN, MAX, SUM, COUNT, or AVG*** to provide more information about each group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Connet to the given database of demo.db3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'Connected: @data/demo.db3'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql sqlite:///data/demo.db3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you do not remember the tables in the demo data, you can always use the following command to query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>hru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>watershed_daily</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>watershed_monthly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>watershed_yearly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>channel_dimension</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>hru_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sub_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>rch_info</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_plant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_annual_hru</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_monthly_basin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ave_annual_basin</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'rch',),\n",
       " (u'hru',),\n",
       " (u'sub',),\n",
       " (u'sed',),\n",
       " (u'watershed_daily',),\n",
       " (u'watershed_monthly',),\n",
       " (u'watershed_yearly',),\n",
       " (u'channel_dimension',),\n",
       " (u'hru_info',),\n",
       " (u'sub_info',),\n",
       " (u'rch_info',),\n",
       " (u'ave_plant',),\n",
       " (u'ave_annual_hru',),\n",
       " (u'ave_monthly_basin',),\n",
       " (u'ave_annual_basin',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT name FROM sqlite_master WHERE type='table'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Grouping data\n",
    "\n",
    "Take the table of ***rch*** as an example\n",
    "\n",
    "#### 2.1 Check the table colums firstly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>FLOW_INcms</th>\n",
       "        <th>FLOW_OUTcms</th>\n",
       "        <th>EVAPcms</th>\n",
       "        <th>TLOSScms</th>\n",
       "        <th>SED_INtons</th>\n",
       "        <th>SED_OUTtons</th>\n",
       "        <th>SEDCONCmg_kg</th>\n",
       "        <th>ORGN_INkg</th>\n",
       "        <th>ORGN_OUTkg</th>\n",
       "        <th>ORGP_INkg</th>\n",
       "        <th>ORGP_OUTkg</th>\n",
       "        <th>NO3_INkg</th>\n",
       "        <th>NO3_OUTkg</th>\n",
       "        <th>NH4_INkg</th>\n",
       "        <th>NH4_OUTkg</th>\n",
       "        <th>NO2_INkg</th>\n",
       "        <th>NO2_OUTkg</th>\n",
       "        <th>MINP_INkg</th>\n",
       "        <th>MINP_OUTkg</th>\n",
       "        <th>CHLA_INkg</th>\n",
       "        <th>CHLA_OUTkg</th>\n",
       "        <th>CBOD_INkg</th>\n",
       "        <th>CBOD_OUTkg</th>\n",
       "        <th>DISOX_INkg</th>\n",
       "        <th>DISOX_OUTkg</th>\n",
       "        <th>SOLPST_INmg</th>\n",
       "        <th>SOLPST_OUTmg</th>\n",
       "        <th>SORPST_INmg</th>\n",
       "        <th>SORPST_OUTmg</th>\n",
       "        <th>REACTPSTmg</th>\n",
       "        <th>VOLPSTmg</th>\n",
       "        <th>SETTLPSTmg</th>\n",
       "        <th>RESUSP_PSTmg</th>\n",
       "        <th>DIFFUSEPSTmg</th>\n",
       "        <th>REACBEDPSTmg</th>\n",
       "        <th>BURYPSTmg</th>\n",
       "        <th>BED_PSTmg</th>\n",
       "        <th>BACTP_OUTct</th>\n",
       "        <th>BACTLP_OUTct</th>\n",
       "        <th>CMETAL_1kg</th>\n",
       "        <th>CMETAL_2kg</th>\n",
       "        <th>CMETAL_3kg</th>\n",
       "        <th>TOT_Nkg</th>\n",
       "        <th>TOT_Pkg</th>\n",
       "        <th>NO3ConcMg_l</th>\n",
       "        <th>WTMPdegc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>146.343765259</td>\n",
       "        <td>146.252487183</td>\n",
       "        <td>0.0912808850408</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2.33204616507e-07</td>\n",
       "        <td>61619.4648438</td>\n",
       "        <td>155.371902466</td>\n",
       "        <td>0.0160862877965</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0482588782907</td>\n",
       "        <td>0.0</td>\n",
       "        <td>362.048675537</td>\n",
       "        <td>361.81350708</td>\n",
       "        <td>203.620849609</td>\n",
       "        <td>421.183776855</td>\n",
       "        <td>0.0</td>\n",
       "        <td>23.0184326172</td>\n",
       "        <td>0.0161072444171</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1.18390523079e-11</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>5627225.0</td>\n",
       "        <td>5623486.5</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>806.01574707</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>96.225692749</td>\n",
       "        <td>96.1828536987</td>\n",
       "        <td>0.0428212843835</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1.64267646596e-07</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0136315366253</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0408946201205</td>\n",
       "        <td>0.0</td>\n",
       "        <td>315.600524902</td>\n",
       "        <td>315.457977295</td>\n",
       "        <td>0.0</td>\n",
       "        <td>127.005020142</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0136560499668</td>\n",
       "        <td>0.0</td>\n",
       "        <td>4.13697217716e-16</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>3757606.5</td>\n",
       "        <td>3698301.5</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>442.463012695</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>11.9527187347</td>\n",
       "        <td>11.8613681793</td>\n",
       "        <td>0.0913518294692</td>\n",
       "        <td>0.0</td>\n",
       "        <td>2.03258238685e-07</td>\n",
       "        <td>2.03258238685e-07</td>\n",
       "        <td>6.59506094181e-09</td>\n",
       "        <td>0.0114662880078</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0343988612294</td>\n",
       "        <td>0.00911803822964</td>\n",
       "        <td>48.2963752747</td>\n",
       "        <td>47.9315032959</td>\n",
       "        <td>0.0</td>\n",
       "        <td>62.4676208496</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0114851053804</td>\n",
       "        <td>0.0</td>\n",
       "        <td>5.9410287833e-14</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>360979.90625</td>\n",
       "        <td>456115.90625</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>110.399124146</td>\n",
       "        <td>0.00911803822964</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1981, 1, 146.34376525878906, 146.2524871826172, 0.09128088504076004, 0.0, 2.3320461650655488e-07, 61619.46484375, 155.3719024658203, 0.016086287796497345, 0.0, 0.04825887829065323, 0.0, 362.0486755371094, 361.8135070800781, 203.620849609375, 421.18377685546875, 0.0, 23.0184326171875, 0.016107244417071342, 0.0, 1.1839052307949238e-11, 0.0, 0.0, 0.0, 5627225.0, 5623486.5, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 806.0157470703125, 0.0, 0.0, 0.0),\n",
       " (2, 1981, 1, 96.22569274902344, 96.18285369873047, 0.042821284383535385, 0.0, 1.6426764659627224e-07, 0.0, 0.0, 0.01363153662532568, 0.0, 0.04089462012052536, 0.0, 315.60052490234375, 315.4579772949219, 0.0, 127.00502014160156, 0.0, 0.0, 0.013656049966812134, 0.0, 4.136972177163251e-16, 0.0, 0.0, 0.0, 3757606.5, 3698301.5, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 442.4630126953125, 0.0, 0.0, 0.0),\n",
       " (3, 1981, 1, 11.952718734741211, 11.861368179321289, 0.09135182946920395, 0.0, 2.0325823868461157e-07, 2.0325823868461157e-07, 6.595060941805286e-09, 0.01146628800779581, 0.0, 0.03439886122941971, 0.009118038229644299, 48.2963752746582, 47.93150329589844, 0.0, 62.467620849609375, 0.0, 0.0, 0.011485105380415916, 0.0, 5.941028783295818e-14, 0.0, 0.0, 0.0, 360979.90625, 456115.90625, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 110.39912414550781, 0.009118038229644299, 0.0, 0.0)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * From rch LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.2 Check unique values\n",
    "\n",
    "Firstly, let's have a look at the number of RCH. We can use the ***DISTINCT*** keyword in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>nRCH</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(23,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT COUNT(DISTINCT RCH) AS nRCH\n",
    "FROM rch"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also can use the ***GROUP BY*** clause to get back a cleaner output, with fewer rows – only unique values returned. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1,),\n",
       " (2,),\n",
       " (3,),\n",
       " (4,),\n",
       " (5,),\n",
       " (6,),\n",
       " (7,),\n",
       " (8,),\n",
       " (9,),\n",
       " (10,),\n",
       " (11,),\n",
       " (12,),\n",
       " (13,),\n",
       " (14,),\n",
       " (15,),\n",
       " (16,),\n",
       " (17,),\n",
       " (18,),\n",
       " (19,),\n",
       " (20,),\n",
       " (21,),\n",
       " (22,),\n",
       " (23,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH\n",
    "FROM rch\n",
    "GROUP BY RCH"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.3 Use aggregate  functions on Groups\n",
    "\n",
    "We can get more details trough aggregating data on group than on whole columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>AVG(FLOW_INcms)</th>\n",
       "        <th>AVG(FLOW_OUTcms)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>631.899009331</td>\n",
       "        <td>629.738051465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>333.020218709</td>\n",
       "        <td>332.79249325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>152.367081571</td>\n",
       "        <td>151.586389137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>270.922894329</td>\n",
       "        <td>270.582995496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>933.151283921</td>\n",
       "        <td>921.758211539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>2700.08820021</td>\n",
       "        <td>2697.80292928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>69.6431509788</td>\n",
       "        <td>68.5322956263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1784.0465295</td>\n",
       "        <td>1776.270392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1689.84384275</td>\n",
       "        <td>1688.77430017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>233.960108572</td>\n",
       "        <td>233.507747935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1626.52365745</td>\n",
       "        <td>1614.5366679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>595.446399325</td>\n",
       "        <td>594.383924172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>485.057906775</td>\n",
       "        <td>484.519170675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>218.494237032</td>\n",
       "        <td>218.453753385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>78.6351266844</td>\n",
       "        <td>76.1478577332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>281.742687305</td>\n",
       "        <td>280.690217043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>37.7926779069</td>\n",
       "        <td>37.7048874576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>134.392934138</td>\n",
       "        <td>133.768577119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>84.2512031651</td>\n",
       "        <td>84.1577095128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>150.647510237</td>\n",
       "        <td>150.419515705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>65.1710743587</td>\n",
       "        <td>65.1012784329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>240.036358692</td>\n",
       "        <td>238.239092258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>68.1851837689</td>\n",
       "        <td>67.9537426029</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 631.8990093310674, 629.7380514648225),\n",
       " (2, 333.0202187087801, 332.79249324997267),\n",
       " (3, 152.36708157062532, 151.58638913697666),\n",
       " (4, 270.92289432916374, 270.5829954958624),\n",
       " (5, 933.1512839211358, 921.7582115385268),\n",
       " (6, 2700.088200208876, 2697.8029292848373),\n",
       " (7, 69.64315097878377, 68.53229562627091),\n",
       " (8, 1784.046529504988, 1776.2703920046488),\n",
       " (9, 1689.8438427501255, 1688.7743001672957),\n",
       " (10, 233.96010857224465, 233.50774793492423),\n",
       " (11, 1626.5236574543846, 1614.5366679032643),\n",
       " (12, 595.4463993251323, 594.3839241716597),\n",
       " (13, 485.0579067746798, 484.5191706750128),\n",
       " (14, 218.49423703187043, 218.45375338461665),\n",
       " (15, 78.63512668444051, 76.14785773319956),\n",
       " (16, 281.7426873054769, 280.69021704279714),\n",
       " (17, 37.79267790690064, 37.704887457564475),\n",
       " (18, 134.39293413791393, 133.76857711888022),\n",
       " (19, 84.25120316512054, 84.1577095127768),\n",
       " (20, 150.6475102369984, 150.41951570461194),\n",
       " (21, 65.17107435870501, 65.10127843287256),\n",
       " (22, 240.03635869224865, 238.23909225828118),\n",
       " (23, 68.185183768885, 67.95374260288146)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, AVG(FLOW_INcms), AVG(FLOW_OUTcms)\n",
    "FROM rch\n",
    "GROUP BY RCH"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Order/Sort Records\n",
    "\n",
    "Firstly, let us check the years and months with the maximum FLOW_INcms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>MAX(FLOW_INcms)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3758.95239258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>1820.23376465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1981</td>\n",
       "        <td>5</td>\n",
       "        <td>871.117736816</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>1919.54003906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3961.94506836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1992</td>\n",
       "        <td>6</td>\n",
       "        <td>10503.7236328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1992</td>\n",
       "        <td>3</td>\n",
       "        <td>352.160858154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7693.54345703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7507.53515625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>1513.65100098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7375.59814453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>3167.54492188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>3291.47900391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1798.09533691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>2010</td>\n",
       "        <td>2</td>\n",
       "        <td>445.295684814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1999</td>\n",
       "        <td>5</td>\n",
       "        <td>1628.87585449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1995</td>\n",
       "        <td>6</td>\n",
       "        <td>226.178619385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1161.75158691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>637.02142334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1996</td>\n",
       "        <td>6</td>\n",
       "        <td>1081.85876465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>590.975463867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1428.43603516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>604.881103516</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1997, 7, 3758.952392578125),\n",
       " (2, 1997, 7, 1820.2337646484375),\n",
       " (3, 1981, 5, 871.1177368164062),\n",
       " (4, 1997, 7, 1919.5400390625),\n",
       " (5, 1997, 7, 3961.945068359375),\n",
       " (6, 1992, 6, 10503.7236328125),\n",
       " (7, 1992, 3, 352.1608581542969),\n",
       " (8, 1994, 5, 7693.54345703125),\n",
       " (9, 1994, 5, 7507.53515625),\n",
       " (10, 2010, 5, 1513.6510009765625),\n",
       " (11, 1994, 5, 7375.59814453125),\n",
       " (12, 2010, 5, 3167.544921875),\n",
       " (13, 1997, 6, 3291.47900390625),\n",
       " (14, 1997, 6, 1798.0953369140625),\n",
       " (15, 2010, 2, 445.2956848144531),\n",
       " (16, 1999, 5, 1628.8758544921875),\n",
       " (17, 1995, 6, 226.17861938476562),\n",
       " (18, 1997, 6, 1161.7515869140625),\n",
       " (19, 1997, 6, 637.0214233398438),\n",
       " (20, 1996, 6, 1081.8587646484375),\n",
       " (21, 1997, 6, 590.9754638671875),\n",
       " (22, 1997, 6, 1428.43603515625),\n",
       " (23, 1997, 6, 604.881103515625)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, YR, MO, MAX(FLOW_INcms)\n",
    "FROM rch\n",
    "GROUP BY RCH"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is obvious that the year and month columns are not in a natural sort. This is a good time to bring up the ***ORDER BY*** operator, which you can put at the end of a SQL state‐ment (after any WHERE and GROUP BY). We can sort the query results by year, and then month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>ROUND(MAX(FLOW_INcms),2)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1981</td>\n",
       "        <td>5</td>\n",
       "        <td>871.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1992</td>\n",
       "        <td>3</td>\n",
       "        <td>352.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1992</td>\n",
       "        <td>6</td>\n",
       "        <td>10503.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7693.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7507.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7375.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1995</td>\n",
       "        <td>6</td>\n",
       "        <td>226.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1996</td>\n",
       "        <td>6</td>\n",
       "        <td>1081.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>3291.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1798.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1161.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>637.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>590.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1428.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>604.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3758.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>1820.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>1919.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3961.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1999</td>\n",
       "        <td>5</td>\n",
       "        <td>1628.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>2010</td>\n",
       "        <td>2</td>\n",
       "        <td>445.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>1513.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>3167.54</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(3, 1981, 5, 871.12),\n",
       " (7, 1992, 3, 352.16),\n",
       " (6, 1992, 6, 10503.72),\n",
       " (8, 1994, 5, 7693.54),\n",
       " (9, 1994, 5, 7507.54),\n",
       " (11, 1994, 5, 7375.6),\n",
       " (17, 1995, 6, 226.18),\n",
       " (20, 1996, 6, 1081.86),\n",
       " (13, 1997, 6, 3291.48),\n",
       " (14, 1997, 6, 1798.1),\n",
       " (18, 1997, 6, 1161.75),\n",
       " (19, 1997, 6, 637.02),\n",
       " (21, 1997, 6, 590.98),\n",
       " (22, 1997, 6, 1428.44),\n",
       " (23, 1997, 6, 604.88),\n",
       " (1, 1997, 7, 3758.95),\n",
       " (2, 1997, 7, 1820.23),\n",
       " (4, 1997, 7, 1919.54),\n",
       " (5, 1997, 7, 3961.95),\n",
       " (16, 1999, 5, 1628.88),\n",
       " (15, 2010, 2, 445.3),\n",
       " (10, 2010, 5, 1513.65),\n",
       " (12, 2010, 5, 3167.54)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, YR, MO, ROUND(MAX(FLOW_INcms),2)\n",
    "FROM rch\n",
    "GROUP BY RCH\n",
    "ORDER BY YR, MO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, sorting is done with the ***ASC*** operator, which orders the data in ascending order. We can sort in descending order applying the ***DESC*** operatorhe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>ROUND(MAX(FLOW_INcms),2)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>2010</td>\n",
       "        <td>2</td>\n",
       "        <td>445.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>1513.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>3167.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1999</td>\n",
       "        <td>5</td>\n",
       "        <td>1628.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>3291.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1798.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1161.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>637.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>590.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>1428.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>604.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3758.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>1820.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>1919.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3961.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1996</td>\n",
       "        <td>6</td>\n",
       "        <td>1081.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1995</td>\n",
       "        <td>6</td>\n",
       "        <td>226.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7693.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7507.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7375.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1992</td>\n",
       "        <td>3</td>\n",
       "        <td>352.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1992</td>\n",
       "        <td>6</td>\n",
       "        <td>10503.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1981</td>\n",
       "        <td>5</td>\n",
       "        <td>871.12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(15, 2010, 2, 445.3),\n",
       " (10, 2010, 5, 1513.65),\n",
       " (12, 2010, 5, 3167.54),\n",
       " (16, 1999, 5, 1628.88),\n",
       " (13, 1997, 6, 3291.48),\n",
       " (14, 1997, 6, 1798.1),\n",
       " (18, 1997, 6, 1161.75),\n",
       " (19, 1997, 6, 637.02),\n",
       " (21, 1997, 6, 590.98),\n",
       " (22, 1997, 6, 1428.44),\n",
       " (23, 1997, 6, 604.88),\n",
       " (1, 1997, 7, 3758.95),\n",
       " (2, 1997, 7, 1820.23),\n",
       " (4, 1997, 7, 1919.54),\n",
       " (5, 1997, 7, 3961.95),\n",
       " (20, 1996, 6, 1081.86),\n",
       " (17, 1995, 6, 226.18),\n",
       " (8, 1994, 5, 7693.54),\n",
       " (9, 1994, 5, 7507.54),\n",
       " (11, 1994, 5, 7375.6),\n",
       " (7, 1992, 3, 352.16),\n",
       " (6, 1992, 6, 10503.72),\n",
       " (3, 1981, 5, 871.12)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, YR, MO, ROUND(MAX(FLOW_INcms),2)\n",
    "FROM rch\n",
    "GROUP BY RCH\n",
    "ORDER BY YR DESC, MO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Filter data on groups with the HAVING clause\n",
    "\n",
    "Sometimes, we may want to filter out records based on a group or an aggregated value. While the first instinct might be to use a WHERE statement, this actually will not work because the WHERE filters records, and does not filter aggregations. For example, we try to use a WHERE to filter results where MAX(FLOW_INcms) is greater than 3000. This will get a sqlite3.OperationalError of misuse of aggregate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(sqlite3.OperationalError) misuse of aggregate: MAX() [SQL: u'SELECT RCH, YR, MO, MAX(FLOW_INcms) as MAX_FLOWIN\\nFROM rch\\nWHERE MAX_FLOWIN > 3000.0\\nGROUP BY RCH\\nORDER BY YR DESC, MO']\n"
     ]
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, YR, MO, MAX(FLOW_INcms) as MAX_FLOWIN\n",
    "FROM rch\n",
    "WHERE MAX_FLOWIN > 3000.0\n",
    "GROUP BY RCH\n",
    "ORDER BY YR DESC, MO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Under such a case, we cab use the ***HAVING*** clause to specify a filter condition for a group or an aggregate. The HAVING clause is an optional clause of the SELECT statement. We often use the HAVING clause with the GROUP BY clause. The GROUP BY clause groups a set of rows into a set of summary rows or groups. Then the HAVING clause filters groups based on specified conditions. \n",
    "\n",
    "***It is worth noting that the HAVING clause must follow the GROUP BY cluase strictly.***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RCH</th>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>MAX_FLOWIN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>2010</td>\n",
       "        <td>5</td>\n",
       "        <td>3167.54492188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1997</td>\n",
       "        <td>6</td>\n",
       "        <td>3291.47900391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3758.95239258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1997</td>\n",
       "        <td>7</td>\n",
       "        <td>3961.94506836</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7693.54345703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7507.53515625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1994</td>\n",
       "        <td>5</td>\n",
       "        <td>7375.59814453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1992</td>\n",
       "        <td>6</td>\n",
       "        <td>10503.7236328</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(12, 2010, 5, 3167.544921875),\n",
       " (13, 1997, 6, 3291.47900390625),\n",
       " (1, 1997, 7, 3758.952392578125),\n",
       " (5, 1997, 7, 3961.945068359375),\n",
       " (8, 1994, 5, 7693.54345703125),\n",
       " (9, 1994, 5, 7507.53515625),\n",
       " (11, 1994, 5, 7375.59814453125),\n",
       " (6, 1992, 6, 10503.7236328125)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, YR, MO, MAX(FLOW_INcms) as MAX_FLOWIN\n",
    "FROM rch\n",
    "GROUP BY RCH\n",
    "HAVING MAX_FLOWIN > 3000.0\n",
    "ORDER BY YR DESC, MO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary\n",
    "\n",
    "In this notebook, we learned how to use the DISTINCT operator to get distinct results in our queries and eliminate duplicates.\n",
    "\n",
    "Next, we learned how to aggregate and sort data using GROUP BY and ORDER BY. \n",
    "\n",
    "We also showed the power of the aggregate functions of SUM(), MAX(), MIN(), AVG(), and COUNT(). \n",
    "\n",
    "Furthermore, we used the HAVING clause to filter aggregated fields that can not be done with the WHERE clause. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}