2. Group By Statement.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GROUP BY Statements\n",
    "\n",
    "### In this notebook we study some use case examples of the GROUP BY statments. We use Python as our main programming language and make use of libraries such as Pandas ans Psycopg2 to present the results of the queries.\n",
    "\n",
    "# Aggregate Functions\n",
    "\n",
    "Before we study the GROUP BY statements we must learn about aggregate functions. SQL provides us with a large number of aggregate function but we'll just cover a few of them here, the ones that I belive are the most common. \n",
    "\n",
    "The main idea behind the aggregate functions is to take several inputs and return a single input. In our particular case, we'll call an aggregate function on a given column (or a subset of the column) and it will return a single value made out of the values of the column.\n",
    "\n",
    "In fact we´ve already seen an example of an aggregate function. The COUNT() function takes a set of rows and returns the number of rows.\n",
    "\n",
    "In order to start coding, we have call the pandas and psycopg2 libraries and create a connection to communicate with the database.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import psycopg2 as pg2\n",
    "\n",
    "connection = pg2.connect(database = 'dvdrental', user = 'postgres', password = 'password')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just like in the prevous notebook, we´ll define the get_data function in order to prevent memory problems and to make our code more clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_data(query, rows = 10):\n",
    "\n",
    "    with connection.cursor() as cursor:\n",
    "        cursor. execute(query)\n",
    "\n",
    "        if rows == 'all':\n",
    "            raw_data = cursor.fetchall()\n",
    "        else:\n",
    "            raw_data = cursor.fetchmany(rows) \n",
    "\n",
    "        col_names = [col_desc[0] for col_desc in cursor.description]\n",
    "        data = pd.DataFrame(raw_data, columns = col_names)\n",
    "\n",
    "    return data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 1 (AVG)\n",
    "\n",
    "We can use the AVG (average function) to calculate the average replacement cost of the movies in the database "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19.9840000000000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   avg\n",
       "0  19.9840000000000000"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_1 = 'SELECT AVG(replacement_cost) FROM film'\n",
    "av_rc = get_data(query_1)\n",
    "\n",
    "av_rc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 2 (MAX)\n",
    "\n",
    "We can ask for the maximum replacement cost. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     max\n",
       "0  29.99"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_2 = 'SELECT MAX(replacement_cost) FROM film'\n",
    "max_rc = get_data(query_2)\n",
    "\n",
    "max_rc "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The case in which we ask for the minimum value is totally analogous, we just have to replace the MAX() function for the MIN() function. \n",
    "\n",
    "## Ex. 3 (SUM)\n",
    "\n",
    "We use the SUM() function to obtain the sum of all the vlaues in a column. In the following, we calculate the sum of all the replacement costs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19984.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        sum\n",
       "0  19984.00"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_3 = 'SELECT SUM(replacement_cost) FROM film'\n",
    "sum_rc = get_data(query_3)\n",
    "\n",
    "sum_rc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 4\n",
    "\n",
    "To conclude our brief discussion on aggregate functions, I would just like to add the fact that we can call several aggregate functions at the same time on different columns, for example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>sum</th>\n",
       "      <th>avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19984.00</td>\n",
       "      <td>115.2720000000000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        sum                   avg\n",
       "0  19984.00  115.2720000000000000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_4 = '''\n",
    "          SELECT SUM(replacement_cost), AVG(length) \n",
    "          FROM film\n",
    "          '''\n",
    "custom_query = get_data(query_4)\n",
    "\n",
    "custom_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GROUP BY Statement\n",
    "\n",
    "### The GROUP BY statement is used to group subsets of rows based on their values in a given field. The function performs this grouping by making use of the aggregate functions. The GROUP BY statement splits the rows into categories based on their value in a certain field and then it applies the aggregate function on each of those categories separately.\n",
    "\n",
    "# Ex. 5\n",
    "\n",
    "Suppose we want to know the total amount of money spent per customer. To achieve this we would have sum the amount of all the payments made by each of the customer separately. In other words we want to know the total amount per customer. The following lines of code carries on this task\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>customer_id</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>184</td>\n",
       "      <td>80.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>87</td>\n",
       "      <td>137.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>477</td>\n",
       "      <td>106.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>273</td>\n",
       "      <td>130.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>550</td>\n",
       "      <td>151.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>51</td>\n",
       "      <td>123.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>394</td>\n",
       "      <td>77.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>272</td>\n",
       "      <td>65.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>70</td>\n",
       "      <td>75.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>190</td>\n",
       "      <td>102.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id     sum\n",
       "0          184   80.80\n",
       "1           87  137.72\n",
       "2          477  106.79\n",
       "3          273  130.72\n",
       "4          550  151.69\n",
       "5           51  123.70\n",
       "6          394   77.80\n",
       "7          272   65.87\n",
       "8           70   75.83\n",
       "9          190  102.75"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_5 = '''\n",
    "          SELECT customer_id, SUM(amount) \n",
    "          FROM payment\n",
    "          GROUP BY customer_id\n",
    "          '''\n",
    "amount_per_cust = get_data(query_5)\n",
    "\n",
    "amount_per_cust"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ex. 6\n",
    "\n",
    "If we wanted to know who are the five customers that have spent the most amount of money, we can make the following query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>customer_id</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>148</td>\n",
       "      <td>211.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>526</td>\n",
       "      <td>208.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>178</td>\n",
       "      <td>194.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>137</td>\n",
       "      <td>191.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>144</td>\n",
       "      <td>189.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id     sum\n",
       "0          148  211.55\n",
       "1          526  208.58\n",
       "2          178  194.61\n",
       "3          137  191.62\n",
       "4          144  189.60"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_6 = '''\n",
    "          SELECT customer_id, SUM(amount) \n",
    "          FROM payment\n",
    "          GROUP BY customer_id \n",
    "          ORDER BY SUM(amount) DESC\n",
    "          LIMIT 5\n",
    "          '''\n",
    "top_customers = get_data(query_6)\n",
    "\n",
    "top_customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex. 7\n",
    "\n",
    "We can also group by multiple columns, for example, we could ask for the maximum amount spent per customer_id per staff_id and then order by customer_id and then we perform a suborder by staff_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>customer_id</th>\n",
       "      <th>staff_id</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>60.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>53.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>55.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>67.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>59.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>70.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>49.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>31.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>63.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>70.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id  staff_id    sum\n",
       "0            1         1  60.85\n",
       "1            1         2  53.85\n",
       "2            2         1  55.86\n",
       "3            2         2  67.88\n",
       "4            3         1  59.88\n",
       "5            3         2  70.88\n",
       "6            4         1  49.88\n",
       "7            4         2  31.90\n",
       "8            5         1  63.86\n",
       "9            5         2  70.79"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_7 = '''\n",
    "          SELECT customer_id, staff_id, SUM(amount) \n",
    "          FROM payment\n",
    "          GROUP BY customer_id, staff_id\n",
    "          ORDER BY customer_id, staff_id\n",
    "          '''\n",
    "max_amount = get_data(query_7)\n",
    "\n",
    "max_amount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## To conclude this notebook we must close the connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.3 64-bit ('sql': conda)",
   "language": "python",
   "name": "python38364bitsqlcondac46ff261eb2d405f8ac307a3c81d2935"
  },
  "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}