SQL 4 - Group By, Having & Count.ipynb

{
  "cells": [
    {
      "metadata": {
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
      },
      "cell_type": "markdown",
      "source": "# Intro\n\nNow that we know how to select the content of a column, we'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\nTo do this, we're going to learn about three new techniques: GROUP BY, HAVING and COUNT. Once again, we're going to use this 100% made up table of information on various pets, which has three columns: one with the unique ID number for each pet, one with the name of the pet and one with the species of the animal (rabbit, cat or dog). \n\n![](https://i.imgur.com/Ef4Puo3.png)\n\n### COUNT\n___\n\nCOUNT(), 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. So if we SELECT the COUNT() of the ID column, it will return the number of ID's in that column.\n\n    SELECT COUNT(ID)\n    FROM `bigquery-public-data.pet_records.pets`\n    \nThis query, based on the table above, will return 4 because there are 4 ID's in this table.\n \n### GROUP BY\n___\n\nGROUP BY takes the name of one or more column and tells SQL that we want to treat rows that have the same value in that column as a single group when we apply aggregate functions like COUNT().\n\n> An **aggregate function** takes in many values and returns one. Here, we're learning about COUNT() but there are other aggregate functions like SUM() and AVERAGE().\n\nNote that because it tells SQL how to apply aggregate functions, it doesn't make sense to use GROUP BY without something like COUNT(). \n\nLet's look at an example. We want to know how many of each type of animal we have in our table. We can get this information by using 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. You can see the general idea in this image:\n\n![](https://i.imgur.com/MFRhycu.png)\n\nThe query that will get us this information looks like this:\n\n    SELECT Animal, COUNT(ID)\n    FROM `bigquery-public-data.pet_records.pets`\n    GROUP BY Animal\n\nThis query will return a table with two columns (Animal & COUNT(ID)) three rows (one for each distinct Animal). \n\nOne thing to note is that if you SELECT a column that you don't pass to 1) GROUP BY or 2) use as input to an aggregate function, you'll get an error. So this query won't work, because the Name column isn't either passed to either an aggregate function or a GROUP BY clause:\n\n    # NOT A VALID QUERY! \"Name\" isn't passed to GROUP BY\n    # or an aggregate function\n    SELECT Name, Animal, COUNT(ID)\n    FROM `bigquery-public-data.pet_records.pets`\n    GROUP BY Animal\n    \nIf 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### GROUP BY ... HAVING\n___\n\nAnother option you have when using GROUP BY is to specify that you want to ignore groups that don't meet certain criteria. So this query, for example, will only include groups that have more than one ID in them:\n\n    SELECT Animal, COUNT(ID)\n    FROM `bigquery-public-data.pet_records.pets`\n    GROUP BY Animal\n    HAVING COUNT(ID) > 1\n\nThe only group that this query will return information on is the one in the cells highlighted in blue in this figure:\n\n![](https://i.imgur.com/8xutHzn.png)\n\nAs a result, this query will return a table with only one row, since this there only one group remaining. It will have two columns: one for \"Animal\", which will have \"Cat\" in it, and one for COUNT(ID), which will have 2 in it. \n\n## Example: Which Hacker News comments generated the most discussion?\n___\n\nNow we're ready to work through an example on a real dataset. Today, we're going to be using the Hacker News dataset, which contains information on stories & comments from the Hacker News social networking site. I want to know which comments on the site generated the most replies.\n\nI already know that I want the \"comments\" table, so I'm going to look at the first couple of rows of that to get started."
    },
    {
      "metadata": {
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# import package with helper functions \nimport bq_helper\n\n# create a helper object for this dataset\nhacker_news = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n                                   dataset_name=\"hacker_news\")\n\n# print the first couple rows of the \"comments\" table\nhacker_news.head(\"comments\")",
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 1,
          "data": {
            "text/plain": "         id  by author        time                   time_ts  \\\n0   2701393  5l     5l  1309184881 2011-06-27 14:28:01+00:00   \n1   5811403  99     99  1370234048 2013-06-03 04:34:08+00:00   \n2     21623  AF     AF  1178992400 2007-05-12 17:53:20+00:00   \n3  10159727  EA     EA  1441206574 2015-09-02 15:09:34+00:00   \n4   2988424  Iv     Iv  1315853580 2011-09-12 18:53:00+00:00   \n\n                                                text    parent deleted  dead  \\\n0  And the glazier who fixed all the broken windo...   2701243    None  None   \n1  Does canada have the equivalent of H1B/Green c...   5804452    None  None   \n2  Speaking of Rails, there are other options in ...     21611    None  None   \n3  Humans and large livestock (and maybe even pet...  10159396    None  None   \n4  I must say I reacted in the same way when I re...   2988179    None  None   \n\n   ranking  \n0        0  \n1        0  \n2        0  \n3        0  \n4        0  ",
            "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>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_cell_guid": "3ac9c92d-c2ed-4a3b-8679-931c653b4477",
        "_uuid": "549a1d707edef909933c0f41f7733f0a6a9aa3dd"
      },
      "cell_type": "markdown",
      "source": "By looking at the documentation, I learned that the \"parent\" column has information on the comment that each comment was a reply to and the \"id\" column has the unique id used to identify each comment. So I 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. \n\nBecause I'm more interested in popular comments than unpopular comments, I'm also only going to return the groups that have more than ten id's in them. In other words, I'm only going to look at comments that had more than ten comment replies to them."
    },
    {
      "metadata": {
        "_cell_guid": "82207b41-a222-4cb4-8604-7ed73fe361a6",
        "collapsed": true,
        "_uuid": "15001aa2774dfcb9a9f8078b0181ce5cb178d885",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# query to pass to \nquery = \"\"\"SELECT parent, COUNT(id)\n            FROM `bigquery-public-data.hacker_news.comments`\n            GROUP BY parent\n            HAVING COUNT(id) > 10\n        \"\"\"",
      "execution_count": 2,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "4d780dcc-139f-4948-b9b6-49ca2241b9f8",
        "_uuid": "1b015556457c227d2d165bb88ec76b447390f200"
      },
      "cell_type": "markdown",
      "source": "Now that our query is ready, let's run it (safely!) and store the results in a dataframe: "
    },
    {
      "metadata": {
        "_cell_guid": "3d7a7625-3c56-4e5e-9743-932f12cfbc43",
        "_uuid": "346f9b26cc62e018f0ab051803bfee048b65c563",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# the query_to_pandas_safe method will cancel the query if\n# it would use too much of your quota, with the limit set \n# to 1 GB by default\npopular_stories = hacker_news.query_to_pandas_safe(query)\nprint(popular_stories.head())",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": "    parent  f0_\n0  5371725  258\n1  7833250  259\n2  3652041  260\n3  2403696  261\n4  6889613   11\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "_cell_guid": "2c628a60-145e-4bae-b3d9-483854af30cc",
        "_uuid": "e58fdcee4a47ef5068ace483d4f0b93698c05ecc"
      },
      "cell_type": "markdown",
      "source": "Looks good! From here I could do whatever further analysis or visualization I'd like. \n\n> **Why is the column with the COUNT() data called f0_**? It's called this because COUNT() is the first (and in our case, only) aggregate function we used in this query. If we'd used a second one, it would be called \"f1\\_\", the third would be called \"f2\\_\", and so on. We'll learn how to name the output of aggregate functions later this week.\n\nAnd that should be all you need to get started writing your own kernels with GROUP BY... WHERE and COUNT!"
    },
    {
      "metadata": {
        "_cell_guid": "f1a7ac66-eaf3-4afa-a495-747e67d85259",
        "_uuid": "b9b4d6334d4c9e3634c1daf6c242ff8b7cbe5ba7"
      },
      "cell_type": "markdown",
      "source": "# Your Turn\n\nApply what you've learned in **[these coding exercises](https://www.kaggle.com/dansbecker/exercise-group-by-having-count)**.\n\n----\n\n*This tutorial is part of the [SQL Series](https://www.kaggle.com/learn/sql) on Kaggle Learn.*"
    }
  ],
  "metadata": {
    "language_info": {
      "name": "python",
      "version": "3.6.4",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}