7_GroupingAndCase_DiagramsForHomework.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Grouping and CASE\r\n",
                "\r\n",
                "Course videos: https://www.red-gate.com/hub/university/courses/t-sql/tsql-for-beginners\r\n",
                "\r\n",
                "Course scripts: https://litknd.github.io/TSQLBeginners \r\n",
                "\r\n",
                "# Setup: Create a database\r\n",
                "\r\n",
                "We create a super-simple database to illustrate grouping."
            ],
            "metadata": {
                "azdata_cell_guid": "412e43e2-9ed3-45ba-8c80-87aad32bb061"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE master;\r\n",
                "GO\r\n",
                "IF DB_ID('TSQLSchool') IS NOT NULL\r\n",
                "    ALTER DATABASE TSQLSchool SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n",
                "DROP DATABASE TSQLSchool;\r\n",
                "GO\r\n",
                "CREATE DATABASE TSQLSchool;\r\n",
                "GO\r\n",
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "\r\n",
                "/* Let's invent a new worst practice: \r\n",
                "name your tables after twitter accounts, including leading the\r\n",
                "table name with @, which usually indicates a variable  */\r\n",
                "\r\n",
                "CREATE TABLE dbo.[@dog_rates]\r\n",
                "(\r\n",
                "    doggo VARCHAR(128) NOT NULL,\r\n",
                "    ratingoutof10 INT NOT NULL,\r\n",
                "    ratedby VARCHAR(128) NOT NULL\r\n",
                ");\r\n",
                "GO\r\n",
                "\r\n",
                "INSERT dbo.[@dog_rates]\r\n",
                "(\r\n",
                "    doggo,\r\n",
                "    ratingoutof10,\r\n",
                "    ratedby\r\n",
                ")\r\n",
                "VALUES\r\n",
                "('Stormy', 14, 'Kendar'),\r\n",
                "('Stormy', 13, 'Fletcher'),\r\n",
                "('Fletcher', 14, 'Kendar'),\r\n",
                "('Mister', 14, 'Mister'),\r\n",
                "('Mister', 14, 'Kendar');\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT *\r\n",
                "FROM dbo.[@dog_rates];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "c3cc9934-a46b-4f2e-8054-4334cd6f5cee",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# GROUP BY\r\n",
                "\r\n",
                "## Logical processing order \r\n",
                "\r\n",
                "* FROM\r\n",
                "* ON\r\n",
                "* JOIN\r\n",
                "* WHERE\r\n",
                "* <mark>GROUP BY</mark>\r\n",
                "* WITH CUBE or WITH ROLLUP\r\n",
                "* HAVING\r\n",
                "* SELECT\r\n",
                "* DISTINCT\r\n",
                "* ORDER BY\r\n",
                "* TOP\r\n",
                "\r\n",
                "https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql \r\n",
                "\r\n",
                "## GROUP BY and COUNT()\r\n",
                "\r\n",
                "![group by and count](images/group_count.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "8ec49d44-2072-4bc7-b15e-a04be7a6d998"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    COUNT(*) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "35bc79e5-1800-4a5b-9d17-d06cbc75de90",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## GROUP BY and MAX()\r\n",
                "\r\n",
                "![group by and max](images/group_max.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "c63c6527-ab67-4e53-9f87-01c3da75b8c5"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    MAX(ratingoutof10) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "98f3749a-29f5-4a22-9dfd-e4875f7bbca1",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## GROUP BY and MIN()\r\n",
                "\r\n",
                "![group by and min](images/group_min.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "69714b4a-f7c1-41b1-a860-2ad6b152f418"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    MIN(ratingoutof10) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "52baad0c-e03e-4f17-a88b-ec38a433a902",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## GROUP BY and SUM()\r\n",
                "\r\n",
                "![group by and sum](images/group_sum.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "7ded910a-2309-4064-acd9-84b117148d1b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    SUM(ratingoutof10) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "fe45a3c0-c6b7-4e61-b911-dc709ed1fdc0",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## GROUP BY and AVG()\r\n",
                "\r\n",
                "![group by and avg](images/group_avg.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "15b8db8d-78d9-4da5-b827-1bfa46cc4f06"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "--Is the return value of this correct?\r\n",
                "--Stormy has two ratingoutof10's: 13 and 14\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    AVG(ratingoutof10) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "65b72952-be0a-42f7-bc50-8ff785e78053",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--What about this?\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    AVG(1.0 * ratingoutof10) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;\r\n",
                "GO\r\n",
                "\r\n",
                "\r\n",
                "--Return data type: numeric(38,6) - precision 38, scale 6 "
            ],
            "metadata": {
                "azdata_cell_guid": "39205254-9758-44f0-ad7b-5a4d89c58f67",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# GROUP BY and HAVING\r\n",
                "\r\n",
                "## Logical processing order \r\n",
                "\r\n",
                "* FROM\r\n",
                "* ON\r\n",
                "* JOIN\r\n",
                "* WHERE\r\n",
                "* <mark>GROUP BY</mark>\r\n",
                "* WITH CUBE or WITH ROLLUP\r\n",
                "* <mark>HAVING</mark>\r\n",
                "* SELECT\r\n",
                "* DISTINCT\r\n",
                "* ORDER BY\r\n",
                "* TOP\r\n",
                "\r\n",
                "https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql \r\n",
                "\r\n",
                "## GROUP BY and HAVING with MIN()\r\n",
                "\r\n",
                "![group by and having](images/group_having_min.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "c69ad3fa-9d4c-4a00-8748-851267a90e69"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    MIN(ratingoutof10) as tot\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo\r\n",
                "HAVING \r\n",
                "    MIN(ratingoutof10) < 14;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "8758cfa1-b463-4426-96ea-76a494b80b8d",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## GROUP BY and HAVING with COUNT()\r\n",
                "\r\n",
                "![group by and having](images/group_having_count.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "7feba3da-2962-426c-a5a5-f056bac76837"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "    doggo\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo\r\n",
                "HAVING \r\n",
                "    COUNT(*) > 1;\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "f263fa06-ef47-4863-9ee6-2b45e3f54e19",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# CASE\r\n",
                "\r\n",
                "## \"CASE colname when\" syntax\r\n",
                "\r\n",
                "![case](images/case_colname_when.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "57f47fbf-d1b1-4483-a87a-13e5cb968be0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "use TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "   doggo,\r\n",
                "   CASE ratingoutof10\r\n",
                "       WHEN 14 THEN 'boop'\r\n",
                "       WHEN 15 THEN 'blep'\r\n",
                "       ELSE 'h*ck'\r\n",
                "   END AS correctedrating\r\n",
                "FROM dbo.[@dog_rates];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "789bd527-c2cb-421c-83a9-f772ee92e989",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## \"CASE when condition\" syntax\r\n",
                "\r\n",
                "![case](images/case_when_condition.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "4bc13309-9007-4a42-bb12-e9f5b82393cf"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "SELECT\r\n",
                "   doggo,\r\n",
                "   CASE \r\n",
                "    WHEN ratingoutof10 < 14\r\n",
                "    THEN 'h*ck'\r\n",
                "   END AS correctedrating\r\n",
                "FROM dbo.[@dog_rates];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "ac052282-6821-4b3f-bcd1-56f8d4010591",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## \"CASE when compound condition\" syntax\r\n",
                "\r\n",
                "![case](images/case_when_compoundcondition.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "3fcbeff8-79ea-479f-a3df-58b71d5d50ca"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT\r\n",
                "   doggo +\r\n",
                "   CASE \r\n",
                "    WHEN doggo = ratedby\r\n",
                "        and ratingoutof10 > 10\r\n",
                "    THEN ' is proud puppers'\r\n",
                "    ELSE '!'\r\n",
                "   END AS sentence\r\n",
                "FROM dbo.[@dog_rates];\r\n",
                "GO\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "1e06936f-aecc-4342-b4fe-9de2dc394bd7",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--What if we forgot the \"ELSE\" ?\r\n",
                "\r\n",
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT\r\n",
                "   doggo +\r\n",
                "   CASE \r\n",
                "    WHEN doggo = ratedby\r\n",
                "        and ratingoutof10 > 10\r\n",
                "    THEN ' is proud puppers'\r\n",
                "    --ELSE '!'\r\n",
                "   END AS sentence\r\n",
                "FROM dbo.[@dog_rates];\r\n",
                "GO"
            ],
            "metadata": {
                "azdata_cell_guid": "99673827-96c9-463e-af3e-b87ec729120a",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "# GROUP BY and CASE\r\n",
                "\r\n",
                "![case](images/group_case.png)"
            ],
            "metadata": {
                "azdata_cell_guid": "8c472c1b-7ae2-4f06-81a1-7e281c4fd0a1"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE TSQLSchool;\r\n",
                "GO\r\n",
                "\r\n",
                "SELECT\r\n",
                "    doggo,\r\n",
                "    SUM(\r\n",
                "        CASE WHEN doggo = ratedby\r\n",
                "            THEN 1\r\n",
                "            ELSE 0\r\n",
                "        END\r\n",
                "    ) as selfratings\r\n",
                "FROM dbo.[@dog_rates]\r\n",
                "GROUP BY doggo;"
            ],
            "metadata": {
                "azdata_cell_guid": "5df3484a-8dab-483a-b1d3-2944e7f80afb",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}