08. SQL/tutorial/03. group-by-having-count.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "14791ebd",
   "metadata": {
    "papermill": {
     "duration": 0.010676,
     "end_time": "2021-11-09T00:03:54.486775",
     "exception": false,
     "start_time": "2021-11-09T00:03:54.476099",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Now that you can select raw data, you're ready to learn how to group your data and count things within those groups. This can help you answer questions like: \n",
    "\n",
    "* How many of each kind of fruit has our store sold?\n",
    "* How many species of animal has the vet office treated?\n",
    "\n",
    "To do this, you'll learn about three new techniques: **GROUP BY**, **HAVING** and **COUNT()**. Once again, we'll use this made-up table of information on pets. \n",
    "\n",
    "![](https://i.imgur.com/fI5Pvvp.png)\n",
    "\n",
    "# COUNT()\n",
    "\n",
    "**COUNT()**, as you may have guessed from the name, returns a count of things. If you pass it the name of a column, it will return the number of entries in that column. \n",
    "\n",
    "For instance, if we **SELECT** the **COUNT()** of the `ID` column in the `pets` table, it will return 4, because there are 4 ID's in the table.\n",
    "\n",
    "![](https://i.imgur.com/Eu5HkXq.png)\n",
    "\n",
    "**COUNT()** is an example of an **aggregate function**, which takes many values and returns one. (Other examples of aggregate functions include **SUM()**, **AVG()**, **MIN()**, and **MAX()**.)  As you'll notice in the picture above, aggregate functions introduce strange column names (like `f0__`).  Later in this tutorial, you'll learn how to change the name to something more descriptive.\n",
    " \n",
    "# GROUP BY\n",
    "\n",
    "\n",
    "**GROUP BY** takes the name of one or more columns, and treats all rows with the same value in that column as a single group when you apply aggregate functions like **COUNT()**.\n",
    "\n",
    "For example, say we want to know how many of each type of animal we have in the `pets` table. We can use **GROUP BY** to group together rows that have the same value in the `Animal` column, while using **COUNT()** to find out how many ID's we have in each group. \n",
    "\n",
    "![](https://i.imgur.com/tqE9Eh8.png)\n",
    "\n",
    "It returns a table with three rows (one for each distinct animal).  We can see that the `pets` table contains 1 rabbit, 1 dog, and 2 cats.\n",
    "\n",
    "# GROUP BY ... HAVING\n",
    "\n",
    "**HAVING** is used in combination with **GROUP BY** to ignore groups that don't meet certain criteria. \n",
    "\n",
    "So this query, for example, will only include groups that have more than one ID in them.\n",
    "\n",
    "![](https://i.imgur.com/2ImXfHQ.png)\n",
    "\n",
    "Since only one group meets the specified criterion, the query will return a table with only one row. \n",
    "\n",
    "# Example: Which Hacker News comments generated the most discussion?\n",
    "\n",
    "Ready to see an example on a real dataset? The Hacker News dataset contains information on stories and comments from the Hacker News social networking site. \n",
    "\n",
    "We'll work with the `comments` table and begin by printing the first few rows.  (_We have hidden the corresponding code. To take a peek, click on the \"Code\" button below._)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "653e7f7f",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2021-11-09T00:03:54.522010Z",
     "iopub.status.busy": "2021-11-09T00:03:54.520944Z",
     "iopub.status.idle": "2021-11-09T00:03:55.233012Z",
     "shell.execute_reply": "2021-11-09T00:03:55.233487Z"
    },
    "papermill": {
     "duration": 0.73694,
     "end_time": "2021-11-09T00:03:55.233776",
     "exception": false,
     "start_time": "2021-11-09T00:03:54.496836",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:19: UserWarning: Cannot use bqstorage_client if max_results is set, reverting to fetching data with the tabledata.list endpoint.\n"
     ]
    },
    {
     "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>id</th>\n",
       "      <th>by</th>\n",
       "      <th>author</th>\n",
       "      <th>time</th>\n",
       "      <th>time_ts</th>\n",
       "      <th>text</th>\n",
       "      <th>parent</th>\n",
       "      <th>deleted</th>\n",
       "      <th>dead</th>\n",
       "      <th>ranking</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2701393</td>\n",
       "      <td>5l</td>\n",
       "      <td>5l</td>\n",
       "      <td>1309184881</td>\n",
       "      <td>2011-06-27 14:28:01+00:00</td>\n",
       "      <td>And the glazier who fixed all the broken windo...</td>\n",
       "      <td>2701243</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5811403</td>\n",
       "      <td>99</td>\n",
       "      <td>99</td>\n",
       "      <td>1370234048</td>\n",
       "      <td>2013-06-03 04:34:08+00:00</td>\n",
       "      <td>Does canada have the equivalent of H1B/Green c...</td>\n",
       "      <td>5804452</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21623</td>\n",
       "      <td>AF</td>\n",
       "      <td>AF</td>\n",
       "      <td>1178992400</td>\n",
       "      <td>2007-05-12 17:53:20+00:00</td>\n",
       "      <td>Speaking of Rails, there are other options in ...</td>\n",
       "      <td>21611</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10159727</td>\n",
       "      <td>EA</td>\n",
       "      <td>EA</td>\n",
       "      <td>1441206574</td>\n",
       "      <td>2015-09-02 15:09:34+00:00</td>\n",
       "      <td>Humans and large livestock (and maybe even pet...</td>\n",
       "      <td>10159396</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2988424</td>\n",
       "      <td>Iv</td>\n",
       "      <td>Iv</td>\n",
       "      <td>1315853580</td>\n",
       "      <td>2011-09-12 18:53:00+00:00</td>\n",
       "      <td>I must say I reacted in the same way when I re...</td>\n",
       "      <td>2988179</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id  by author        time                   time_ts  \\\n",
       "0   2701393  5l     5l  1309184881 2011-06-27 14:28:01+00:00   \n",
       "1   5811403  99     99  1370234048 2013-06-03 04:34:08+00:00   \n",
       "2     21623  AF     AF  1178992400 2007-05-12 17:53:20+00:00   \n",
       "3  10159727  EA     EA  1441206574 2015-09-02 15:09:34+00:00   \n",
       "4   2988424  Iv     Iv  1315853580 2011-09-12 18:53:00+00:00   \n",
       "\n",
       "                                                text    parent deleted  dead  \\\n",
       "0  And the glazier who fixed all the broken windo...   2701243    None  None   \n",
       "1  Does canada have the equivalent of H1B/Green c...   5804452    None  None   \n",
       "2  Speaking of Rails, there are other options in ...     21611    None  None   \n",
       "3  Humans and large livestock (and maybe even pet...  10159396    None  None   \n",
       "4  I must say I reacted in the same way when I re...   2988179    None  None   \n",
       "\n",
       "   ranking  \n",
       "0        0  \n",
       "1        0  \n",
       "2        0  \n",
       "3        0  \n",
       "4        0  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"hacker_news\" dataset\n",
    "dataset_ref = client.dataset(\"hacker_news\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)\n",
    "\n",
    "# Construct a reference to the \"comments\" table\n",
    "table_ref = dataset_ref.table(\"comments\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"comments\" table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "213e753e",
   "metadata": {
    "papermill": {
     "duration": 0.01033,
     "end_time": "2021-11-09T00:03:55.255758",
     "exception": false,
     "start_time": "2021-11-09T00:03:55.245428",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Let's use the table to see which comments generated the most replies.  Since:\n",
    "- the `parent` column indicates the comment that was replied to, and \n",
    "- the `id` column has the unique ID used to identify each comment, \n",
    "\n",
    "we can **GROUP BY** the `parent` column and **COUNT()** the `id` column in order to figure out the number of comments that were made as responses to a specific comment.  (_This might not make sense immediately -- take your time here to ensure that everything is clear!_)\n",
    "\n",
    "Furthermore, since we're only interested in popular comments, we'll look at comments with more than ten replies.  So, we'll only return groups **HAVING** more than ten ID's."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7c4c4115",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:03:55.285493Z",
     "iopub.status.busy": "2021-11-09T00:03:55.284438Z",
     "iopub.status.idle": "2021-11-09T00:03:55.286533Z",
     "shell.execute_reply": "2021-11-09T00:03:55.287071Z"
    },
    "papermill": {
     "duration": 0.0187,
     "end_time": "2021-11-09T00:03:55.287234",
     "exception": false,
     "start_time": "2021-11-09T00:03:55.268534",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Query to select comments that received more than 10 replies\n",
    "query_popular = \"\"\"\n",
    "                SELECT parent, COUNT(id)\n",
    "                FROM `bigquery-public-data.hacker_news.comments`\n",
    "                GROUP BY parent\n",
    "                HAVING COUNT(id) > 10\n",
    "                \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "466077d8",
   "metadata": {
    "papermill": {
     "duration": 0.01054,
     "end_time": "2021-11-09T00:03:55.308533",
     "exception": false,
     "start_time": "2021-11-09T00:03:55.297993",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Now that our query is ready, let's run it and store the results in a pandas DataFrame: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3f156b12",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:03:55.333644Z",
     "iopub.status.busy": "2021-11-09T00:03:55.332978Z",
     "iopub.status.idle": "2021-11-09T00:04:00.596859Z",
     "shell.execute_reply": "2021-11-09T00:04:00.597405Z"
    },
    "papermill": {
     "duration": 5.278301,
     "end_time": "2021-11-09T00:04:00.597575",
     "exception": false,
     "start_time": "2021-11-09T00:03:55.319274",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "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>parent</th>\n",
       "      <th>f0_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7021664</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7213378</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4325231</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8979886</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8399209</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    parent  f0_\n",
       "0  7021664   40\n",
       "1  7213378   61\n",
       "2  4325231   62\n",
       "3  8979886   38\n",
       "4  8399209   40"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set up the query (cancel the query if it would use too much of \n",
    "# your quota, with the limit set to 10 GB)\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "query_job = client.query(query_popular, job_config=safe_config)\n",
    "\n",
    "# API request - run the query, and convert the results to a pandas DataFrame\n",
    "popular_comments = query_job.to_dataframe()\n",
    "\n",
    "# Print the first five rows of the DataFrame\n",
    "popular_comments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b43c0f65",
   "metadata": {
    "papermill": {
     "duration": 0.011446,
     "end_time": "2021-11-09T00:04:00.621392",
     "exception": false,
     "start_time": "2021-11-09T00:04:00.609946",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Each row in the `popular_comments` DataFrame corresponds to a comment that received more than ten replies.  For instance, the comment with ID `801208` received `56` replies.\n",
    "\n",
    "# Aliasing and other improvements\n",
    "\n",
    "A couple hints to make your queries even better:\n",
    "- The column resulting from `COUNT(id)` was called `f0__`. That's not a very descriptive name. You can change the name by adding `AS NumPosts` after you specify the aggregation. This is called **aliasing**, and it will be covered in more detail in an upcoming lesson.\n",
    "- If you are ever unsure what to put inside the **COUNT()** function, you can do `COUNT(1)` to count the rows in each group. Most people find it especially readable, because we know it's not focusing on other columns. It also scans less data than if supplied column names (making it faster and using less of your data access quota).\n",
    "\n",
    "Using these tricks, we can rewrite our query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "19c0d00f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:00.648171Z",
     "iopub.status.busy": "2021-11-09T00:04:00.647443Z",
     "iopub.status.idle": "2021-11-09T00:04:04.570495Z",
     "shell.execute_reply": "2021-11-09T00:04:04.569962Z"
    },
    "papermill": {
     "duration": 3.937554,
     "end_time": "2021-11-09T00:04:04.570638",
     "exception": false,
     "start_time": "2021-11-09T00:04:00.633084",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "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>parent</th>\n",
       "      <th>NumPosts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7536283</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4053076</td>\n",
       "      <td>242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2530963</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1934367</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8204007</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    parent  NumPosts\n",
       "0  7536283        45\n",
       "1  4053076       242\n",
       "2  2530963        59\n",
       "3  1934367        70\n",
       "4  8204007        43"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Improved version of earlier query, now with aliasing & improved readability\n",
    "query_improved = \"\"\"\n",
    "                 SELECT parent, COUNT(1) AS NumPosts\n",
    "                 FROM `bigquery-public-data.hacker_news.comments`\n",
    "                 GROUP BY parent\n",
    "                 HAVING COUNT(1) > 10\n",
    "                 \"\"\"\n",
    "\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "query_job = client.query(query_improved, job_config=safe_config)\n",
    "\n",
    "# API request - run the query, and convert the results to a pandas DataFrame\n",
    "improved_df = query_job.to_dataframe()\n",
    "\n",
    "# Print the first five rows of the DataFrame\n",
    "improved_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ea0c990",
   "metadata": {
    "papermill": {
     "duration": 0.012519,
     "end_time": "2021-11-09T00:04:04.595953",
     "exception": false,
     "start_time": "2021-11-09T00:04:04.583434",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Now you have the data you want, and it has descriptive names. That's good style.\n",
    "\n",
    "# Note on using **GROUP BY**\n",
    "\n",
    "Note that because it tells SQL how to apply aggregate functions (like **COUNT()**), it doesn't make sense to use **GROUP BY** without an aggregate function.  Similarly, if you have any **GROUP BY** clause, then all variables must be passed to either a\n",
    "1. **GROUP BY** command, or\n",
    "2. an aggregation function.\n",
    "\n",
    "Consider the query below:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0b9fb9c0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:04.625562Z",
     "iopub.status.busy": "2021-11-09T00:04:04.624814Z",
     "iopub.status.idle": "2021-11-09T00:04:04.629139Z",
     "shell.execute_reply": "2021-11-09T00:04:04.628587Z"
    },
    "papermill": {
     "duration": 0.020382,
     "end_time": "2021-11-09T00:04:04.629298",
     "exception": false,
     "start_time": "2021-11-09T00:04:04.608916",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "query_good = \"\"\"\n",
    "             SELECT parent, COUNT(id)\n",
    "             FROM `bigquery-public-data.hacker_news.comments`\n",
    "             GROUP BY parent\n",
    "             \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3da284c6",
   "metadata": {
    "papermill": {
     "duration": 0.012596,
     "end_time": "2021-11-09T00:04:04.654865",
     "exception": false,
     "start_time": "2021-11-09T00:04:04.642269",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Note that there are two variables: `parent` and `id`. \n",
    "- `parent` was passed to a **GROUP BY** command (in `GROUP BY parent`), and \n",
    "- `id` was passed to an aggregate function (in `COUNT(id)`).\n",
    "\n",
    "And this query won't work, because the `author` column isn't passed to an aggregate function or a **GROUP BY** clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ca9b24f9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:04.683649Z",
     "iopub.status.busy": "2021-11-09T00:04:04.683017Z",
     "iopub.status.idle": "2021-11-09T00:04:04.686253Z",
     "shell.execute_reply": "2021-11-09T00:04:04.686771Z"
    },
    "papermill": {
     "duration": 0.019373,
     "end_time": "2021-11-09T00:04:04.686992",
     "exception": false,
     "start_time": "2021-11-09T00:04:04.667619",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "query_bad = \"\"\"\n",
    "            SELECT author, parent, COUNT(id)\n",
    "            FROM `bigquery-public-data.hacker_news.comments`\n",
    "            GROUP BY parent\n",
    "            \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed1275aa",
   "metadata": {
    "papermill": {
     "duration": 0.01393,
     "end_time": "2021-11-09T00:04:04.714620",
     "exception": false,
     "start_time": "2021-11-09T00:04:04.700690",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "If make this error, you'll get the error message `SELECT list expression references column (column's name) which is neither grouped nor aggregated at`.\n",
    "\n",
    "# Your turn\n",
    "\n",
    "These aggregations let you write much more interesting queries. Try it yourself with **[these coding exercises](https://www.kaggle.com/kernels/fork/682058)**."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8806061",
   "metadata": {
    "papermill": {
     "duration": 0.012876,
     "end_time": "2021-11-09T00:04:04.740552",
     "exception": false,
     "start_time": "2021-11-09T00:04:04.727676",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/intro-to-sql/discussion) to chat with other learners.*"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.10"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 20.27461,
   "end_time": "2021-11-09T00:04:05.363890",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2021-11-09T00:03:45.089280",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}