I don't fully understand how GROUB BY works...

Hey Derek Lin -

Both GROUP BY and DISTINCT returns the same data in there simplest form.

DISTINCT is used solely for giving you unique data. GROUP BY will do the same behind the scenes.

However, if you want to use an Aggregate function (e.g. SUM, MIN, MAX, COUNT, AVG) on the returned data then you must use GROUP BY.

For example, using the table below can you give me the total Salary for London and New York separately using the DISTINCT keyword?

London total Salary is 400 and New York is 700.

+--------------+----------+
|  Location    |  Salary  |
+--------------+----------+
|   London     |  300     |
|   New York   |  200     |
|   London     |  100     |
|   New York   |  500     |
+--------------+----------+

You can only accomplish this using the GROUP BY clause and SUM function, because it will GROUP the 2 related locations together and SUM the salary column.

SELECT Location, SUM(Salary) AS Salary FROM myTable GROUP BY Location;

+--------------+----------+
|  Location    |  Salary  |
+--------------+----------+
|   London     |  400     |
|   New York   |  700     |
+--------------+----------+

Please take a look at these awesome links below and really focus on the pictorial presentation to visualize what's going on behind the scenes .

• DISTINCT
• GROUP BY

Kristian,

The group by function is always used with aggregate functions such as SUM, AVG, COUNT. A group by query resembles a pivot table in MS Excel that it contains dimension (your slicer(s)) and your metrics (your counts). For example; I want to count the number of products that I have per category (product category).

SELECT category, COUNT(*) AS "product count" FROM products GROUP BY category;

So in this case category is your dimension and product count is your metric. Please note that your basic GROUP BY query will summarize your results, but you will lose visibility of the details.

Hope this helps.

Robert

Someone should reshoot the video, it's using circular definitions and no one is getting it.

It's like what we had in Engineering Law class, "What would be considered reasonable?", "Well, a reasonable thing is what a reasonable person would do.", "Okay, but what's the definition of a reasonable person?", "Well, a reasonable person would only do reasonable things.", "Yeah, right..."