ex06-Doing Math Across Table Columns.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ex06-Doing Math Across Table Columns\n",
    "\n",
    "As we already know, the demo database (i.e., demo.db3) was extraced from a numerical hydrological modeling. The major data types are of integers, decimals, or floating points. It is quite natural that we want to carry out some calculations or statistical analysis. SQL can handle calculations ranging from basic math through advanced statistics.\n",
    "\n",
    "***Basic Math Operators***\n",
    "<li>+ Addition\n",
    "<li>- Subtraction\n",
    "<li>* Multiplication\n",
    "<li>/ Division (returns the quotient only, no remainder)\n",
    "<li>% Modulo (returns just the remainder)\n",
    "<li>^ Exponentiation\n",
    "<li>Others \n",
    "    \n",
    "Let’s try to use the most frequently used SQL math operators on the demo data. Instead of using numbers in queries, we’ll use the names of the columns that contain the numbers. When we execute the query, the calculation will occur on each row of the table.    "
   ]
  },
  {
   "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. Test Math Operator in an easy way\n",
    "\n",
    "Using the SELECT statement, we can easily test the math operators."
   ]
  },
  {
   "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>3+4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(7,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT 3+4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>12 * 4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>48</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(48,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT 12 * 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>12 % 4</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT 12 % 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Rounded</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>123.46</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(123.46,)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT round(123.456,2) as Rounded"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Doing Math Across Table Columns\n",
    "\n",
    "Take the table of watershed_monthly as an example\n",
    "\n",
    "#### 3.1 Check the table colums firstly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///data/demo.db3\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>PREC_mm</th>\n",
       "        <th>SURQ_mm</th>\n",
       "        <th>LATQ_mm</th>\n",
       "        <th>GWQ_mm</th>\n",
       "        <th>PERCOLA_mm</th>\n",
       "        <th>TILEQ_mm</th>\n",
       "        <th>SW_mm</th>\n",
       "        <th>ET_mm</th>\n",
       "        <th>PET_mm</th>\n",
       "        <th>WYLD_mm</th>\n",
       "        <th>SYLD_tons</th>\n",
       "        <th>NO3_SURQ</th>\n",
       "        <th>NO3_LATQ</th>\n",
       "        <th>NO3_PERC</th>\n",
       "        <th>NO3_CROP</th>\n",
       "        <th>N_ORG</th>\n",
       "        <th>P_SOL</th>\n",
       "        <th>P_ORG</th>\n",
       "        <th>TILENO3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>96.2901611328</td>\n",
       "        <td>0.515981376171</td>\n",
       "        <td>0.412546992302</td>\n",
       "        <td>6.68811368942</td>\n",
       "        <td>19.9067058563</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1854.22424316</td>\n",
       "        <td>6.8717417717</td>\n",
       "        <td>12.2690172195</td>\n",
       "        <td>8.68197631836</td>\n",
       "        <td>0.179334715009</td>\n",
       "        <td>0.000217399661778</td>\n",
       "        <td>0.00227017630823</td>\n",
       "        <td>1.31047689915</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.126228243113</td>\n",
       "        <td>0.00011268912931</td>\n",
       "        <td>0.0154068088159</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>2</td>\n",
       "        <td>160.228042603</td>\n",
       "        <td>3.34680223465</td>\n",
       "        <td>0.645278871059</td>\n",
       "        <td>9.14877605438</td>\n",
       "        <td>34.3681221008</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1766.25305176</td>\n",
       "        <td>9.16553211212</td>\n",
       "        <td>14.7731771469</td>\n",
       "        <td>13.8621845245</td>\n",
       "        <td>1.21271717548</td>\n",
       "        <td>0.000626709603239</td>\n",
       "        <td>0.00195567845367</td>\n",
       "        <td>1.17412638664</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.896599590778</td>\n",
       "        <td>0.00056043791119</td>\n",
       "        <td>0.109999984503</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>3</td>\n",
       "        <td>136.652908325</td>\n",
       "        <td>3.82499432564</td>\n",
       "        <td>1.48131656647</td>\n",
       "        <td>18.5184955597</td>\n",
       "        <td>34.3672447205</td>\n",
       "        <td>0.0</td>\n",
       "        <td>1990.75354004</td>\n",
       "        <td>13.5204763412</td>\n",
       "        <td>23.3635005951</td>\n",
       "        <td>25.0185108185</td>\n",
       "        <td>1.26296019554</td>\n",
       "        <td>0.000849568168633</td>\n",
       "        <td>0.0075485506095</td>\n",
       "        <td>0.504496872425</td>\n",
       "        <td>0.0</td>\n",
       "        <td>0.679934620857</td>\n",
       "        <td>0.000544593494851</td>\n",
       "        <td>0.0833883434534</td>\n",
       "        <td>0.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1981, 1, 96.2901611328125, 0.5159813761711121, 0.4125469923019409, 6.688113689422607, 19.906705856323242, 0.0, 1854.2242431640625, 6.871741771697998, 12.269017219543457, 8.681976318359375, 0.17933471500873566, 0.00021739966177847236, 0.0022701763082295656, 1.3104768991470337, 0.0, 0.1262282431125641, 0.00011268912930972874, 0.015406808815896511, 0.0),\n",
       " (1981, 2, 160.22804260253906, 3.346802234649658, 0.6452788710594177, 9.148776054382324, 34.36812210083008, 0.0, 1766.2530517578125, 9.165532112121582, 14.773177146911621, 13.862184524536133, 1.2127171754837036, 0.0006267096032388508, 0.001955678453668952, 1.174126386642456, 0.0, 0.8965995907783508, 0.0005604379111900926, 0.10999998450279236, 0.0),\n",
       " (1981, 3, 136.6529083251953, 3.8249943256378174, 1.4813165664672852, 18.518495559692383, 34.367244720458984, 0.0, 1990.7535400390625, 13.520476341247559, 23.363500595092773, 25.018510818481445, 1.2629601955413818, 0.0008495681686326861, 0.007548550609499216, 0.5044968724250793, 0.0, 0.6799346208572388, 0.0005445934948511422, 0.08338834345340729, 0.0)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * From watershed_monthly LIMIT 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2 Calculate the difference between two colummns\n",
    "\n",
    "For example, we are interested in the difference between Potential evapotranspiration (PET_mm) and precipitation (PREC_mm). It is so-called Potential evapotranspiration deficit (PED). PED can be thought of as a drought index. It is the difference between how much water could potentially be lost from the soil through evapotranspiration and how much is actually available. When PED is high, plants do not have the full amount of water available they need for growth."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>Precipitation</th>\n",
       "        <th>PET</th>\n",
       "        <th>PED</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>96.2901611328</td>\n",
       "        <td>12.2690172195</td>\n",
       "        <td>-84.0211439133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>2</td>\n",
       "        <td>160.228042603</td>\n",
       "        <td>14.7731771469</td>\n",
       "        <td>-145.454865456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>3</td>\n",
       "        <td>136.652908325</td>\n",
       "        <td>23.3635005951</td>\n",
       "        <td>-113.28940773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>4</td>\n",
       "        <td>118.857406616</td>\n",
       "        <td>36.1955604553</td>\n",
       "        <td>-82.6618461609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>5</td>\n",
       "        <td>84.5469818115</td>\n",
       "        <td>89.7725601196</td>\n",
       "        <td>5.22557830811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>6</td>\n",
       "        <td>44.837184906</td>\n",
       "        <td>123.683319092</td>\n",
       "        <td>78.8461341858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>7</td>\n",
       "        <td>32.3259849548</td>\n",
       "        <td>174.008895874</td>\n",
       "        <td>141.682910919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>8</td>\n",
       "        <td>20.6514968872</td>\n",
       "        <td>152.637496948</td>\n",
       "        <td>131.986000061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>9</td>\n",
       "        <td>16.579656601</td>\n",
       "        <td>115.852905273</td>\n",
       "        <td>99.2732486725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>10</td>\n",
       "        <td>59.6729316711</td>\n",
       "        <td>43.6953010559</td>\n",
       "        <td>-15.9776306152</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1981, 1, 96.2901611328125, 12.269017219543457, -84.02114391326904),\n",
       " (1981, 2, 160.22804260253906, 14.773177146911621, -145.45486545562744),\n",
       " (1981, 3, 136.6529083251953, 23.363500595092773, -113.28940773010254),\n",
       " (1981, 4, 118.85740661621094, 36.195560455322266, -82.66184616088867),\n",
       " (1981, 5, 84.54698181152344, 89.7725601196289, 5.225578308105469),\n",
       " (1981, 6, 44.83718490600586, 123.68331909179688, 78.84613418579102),\n",
       " (1981, 7, 32.325984954833984, 174.00889587402344, 141.68291091918945),\n",
       " (1981, 8, 20.65149688720703, 152.6374969482422, 131.98600006103516),\n",
       " (1981, 9, 16.57965660095215, 115.8529052734375, 99.27324867248535),\n",
       " (1981, 10, 59.67293167114258, 43.6953010559082, -15.977630615234375)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT YR, MO,  \n",
    "PREC_mm as Precipitation, \n",
    "PET_mm as PET, \n",
    "PET_mm-PREC_mm as PED \n",
    "From watershed_monthly LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### We also can calculate the PED ratio to Precipitation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>YR</th>\n",
       "        <th>MO</th>\n",
       "        <th>Precipitation</th>\n",
       "        <th>PET</th>\n",
       "        <th>PED_Ratio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>1</td>\n",
       "        <td>96.2901611328</td>\n",
       "        <td>12.2690172195</td>\n",
       "        <td>-87.2582857114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>2</td>\n",
       "        <td>160.228042603</td>\n",
       "        <td>14.7731771469</td>\n",
       "        <td>-90.779905373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>3</td>\n",
       "        <td>136.652908325</td>\n",
       "        <td>23.3635005951</td>\n",
       "        <td>-82.9030344971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>4</td>\n",
       "        <td>118.857406616</td>\n",
       "        <td>36.1955604553</td>\n",
       "        <td>-69.5470720035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>5</td>\n",
       "        <td>84.5469818115</td>\n",
       "        <td>89.7725601196</td>\n",
       "        <td>6.18067989672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>6</td>\n",
       "        <td>44.837184906</td>\n",
       "        <td>123.683319092</td>\n",
       "        <td>175.849876283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>7</td>\n",
       "        <td>32.3259849548</td>\n",
       "        <td>174.008895874</td>\n",
       "        <td>438.294180725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>8</td>\n",
       "        <td>20.6514968872</td>\n",
       "        <td>152.637496948</td>\n",
       "        <td>639.111057091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>9</td>\n",
       "        <td>16.579656601</td>\n",
       "        <td>115.852905273</td>\n",
       "        <td>598.76540909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1981</td>\n",
       "        <td>10</td>\n",
       "        <td>59.6729316711</td>\n",
       "        <td>43.6953010559</td>\n",
       "        <td>-26.7753404564</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1981, 1, 96.2901611328125, 12.269017219543457, -87.25828571143332),\n",
       " (1981, 2, 160.22804260253906, 14.773177146911621, -90.7799053730202),\n",
       " (1981, 3, 136.6529083251953, 23.363500595092773, -82.90303449708202),\n",
       " (1981, 4, 118.85740661621094, 36.195560455322266, -69.54707200351655),\n",
       " (1981, 5, 84.54698181152344, 89.7725601196289, 6.180679896716599),\n",
       " (1981, 6, 44.83718490600586, 123.68331909179688, 175.84987628255342),\n",
       " (1981, 7, 32.325984954833984, 174.00889587402344, 438.29418072534986),\n",
       " (1981, 8, 20.65149688720703, 152.6374969482422, 639.1110570914423),\n",
       " (1981, 9, 16.57965660095215, 115.8529052734375, 598.7654090904647),\n",
       " (1981, 10, 59.67293167114258, 43.6953010559082, -26.775340456351415)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT YR, MO,  PREC_mm as Precipitation, \n",
    "PET_mm as PET, \n",
    "(PET_mm-PREC_mm)/PREC_mm*100.0 as PED_Ratio \n",
    "From watershed_monthly LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.3 Use math operators in a WHERE statement\n",
    "\n",
    "For example, we could use the modulus operator (%) to filter the MOs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>FLOW_INcms</th>\n",
       "        <th>FLOW_OUTcms</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>3</td>\n",
       "        <td>3.42989563942</td>\n",
       "        <td>3.08460569382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>6</td>\n",
       "        <td>736.301635742</td>\n",
       "        <td>734.99206543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>9</td>\n",
       "        <td>218.407974243</td>\n",
       "        <td>218.244567871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>2010</td>\n",
       "        <td>12</td>\n",
       "        <td>36.955821991</td>\n",
       "        <td>36.8774642944</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10, 2010, 3, 3.4298956394195557, 3.0846056938171387),\n",
       " (10, 2010, 6, 736.3016357421875, 734.9920654296875),\n",
       " (10, 2010, 9, 218.40797424316406, 218.24456787109375),\n",
       " (10, 2010, 12, 36.9558219909668, 36.877464294433594)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT RCH, YR, MO, FLOW_INcms, FLOW_OUTcms \n",
    "From rch \n",
    "WHERE YR>2009 \n",
    "and RCH=10 \n",
    "and MO % 3 = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Do some statistics with Aggregate Functions\n",
    "\n",
    "So far, we’ve performed math operations across columns in each row of a table. We also can calculate a result from values within the same column using aggregate function, which calculate a single result from multiple inputs. Two of the most-used aggregate functions in data analysis are avg() and sum()."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.1 average\n",
    "\n",
    "avg - calculates the average of all values in that column (omits null values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>avg(FLOW_INcms)</th>\n",
       "        <th>avg(FLOW_OUTcms)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>559.361707683</td>\n",
       "        <td>557.279226083</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(559.361707683415, 557.2792260827198)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT avg(FLOW_INcms), avg(FLOW_OUTcms) \n",
    "From rch "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.2 sum\n",
    "\n",
    "sum - calculates the sum of the values in that column (omits null values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>sum(FLOW_INcms)</th>\n",
       "        <th>sum(FLOW_OUTcms)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4631514.93962</td>\n",
       "        <td>4614271.99196</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4631514.939618677, 4614271.9919649195)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT sum(FLOW_INcms), sum(FLOW_OUTcms) \n",
    "From rch "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3 extreme values\n",
    "\n",
    "max - calculates the maximum value in that column (omits null values).\n",
    "\n",
    "min - calculates the minimum value in that column (omits null values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min(FLOW_INcms)</th>\n",
       "        <th>max(FLOW_OUTcms)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0.201215535402</td>\n",
       "        <td>10499.5498047</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0.20121553540229797, 10499.5498046875)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT min(FLOW_INcms), max(FLOW_OUTcms) \n",
    "From rch "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Calculate by ourselves\n",
    "\n",
    "We can calculated some values by the combination of those math operators. For example, we can calculate the average values of Flow_In and Flow_Out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>AVG_FlowIn</th>\n",
       "        <th>AVG_FlowOut</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>559.361707683</td>\n",
       "        <td>557.279226083</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(559.361707683415, 557.2792260827198)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql sqlite://\n",
    "SELECT sum(FLOW_INcms)/COUNT(FLOW_INcms) as AVG_FlowIn, \n",
    "sum(FLOW_OUTcms)/COUNT(FLOW_OUTcms) as AVG_FlowOut\n",
    "From rch "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary\n",
    "\n",
    "Aggregating data (also referred to as rolling up, summarizing, or grouping data) is creating some sort of total from a number of records. Sum, min, max, count, and average are common aggregate operations.\n",
    "\n",
    "In fact, the above example did not present the real power of these aggregation functions. They will become more powerful only when they are used with ***GROUP BY*** and ***ORDER BY*** clauses."
   ]
  },
  {
   "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
}