Sams Teach Yourself SQL in 10 minutes - Question about GROUP BY

i read the book "Sams Teach Yourself SQL in 10 minutes, Third Edition" and in the lesson 10 "Grouping Data", section "Creating Groups", i can't understand the following:

"Aside from the aggregate calculations statements, every column in your SELECT statement must be present in the GROUP BY clause."

Why? I tried this and i think that it is not true. For example, consider a table 'World' with the columns 'continent', 'country', 'population'.

SELECT continent, country
FROM World
GROUP BY continent;

According to the book, this should lead to an error, right? But it doesn't. I can group my data depending on the continent (so we have at the results 7 continents) and next to each continent, a random country name.

Like this

continent         country
开发者_开发技巧North America     Canada
South America     Brazil
Europe            France
Africa            Cameroon
Asia              Japan
Australia         New Zealand
Antarctica        TuxLand

You are most probably using MySQL which allows ungrouped and unaggregated expressions in SELECT clause.

This is violation of standard of course.

This is intended to simplify GROUP BY with joins on a PRIMARY KEY:

SELECT  a.*, SUM(b.value)
FROM    a
JOIN    b
ON      b.a_id = a.id
GROUP BY
        a.id

Normally, you would have either to add all columns from a into the GROUP BY clause or use a subquery.

MySQL allows you not to do it since all values from a are guaranteed to be the same for a given value of the PRIMARY KEY (which is grouped on).

This is correct and should produce no error in some forms of SQL such as MySQL. You may optionally use the GROUP BY statement on more than one column but it's not required.

GROUP BY will list the first result of the columns specified - so in your case, it would return the first country/continent pair.

PostgreSQL and MySQL allow this, using one field for the group by.

The tutorial probably assumes you should use GROUP BY on all fields so from what you select, you don't lose any data - it would show every country/continent in the above example, but only once.

Here's an example table:

Continent      |  Country     | Random_Field
---------------------------------------------
North America     Canada          Cake
North America     Canada          Dog
South America     Brazil          Cat
Europe            France          Frog
Africa            Cameroon        House
Asia              Japan           Gadget
Asia              India           Dance
Australia         New Zealand     Frodo
Antarctica        TuxLand         Linux

In your first statement:

SELECT continent, country
FROM World
GROUP BY continent;

The output would be:

Continent      |  Country     
--------------------------
North America     Canada   
South America     Brazil 
Europe            France  
Africa            Cameroon   
Asia              Japan    
Australia         New Zealand 
Antarctica        TuxLand  

Notice one of the Asia rows was lost, despite being different.

Using a GROUP BY on both:

SELECT continent, country
FROM World
GROUP BY continent, country;

Would yield:

   Continent      |  Country     
    -----------------------------
    North America     Canada   
    South America     Brazil    
    Europe            France    
    Africa            Cameroon    
    Asia              Japan      
    Asia              India      
    Australia         New Zealand 
    Antarctica        TuxLand