how does the groupby and count work in sql

Question

1> select browser,count(*) from logtest group by browser;

+-----------+----------+
| browser   | count(*) |
+-----------+----------+
| Firefox 3 |       14 |
| Unknown   |       11 |
+-----------+----------+

2 rows in set

2> select browser,count(browser) from logtest group by browser;

+-----------+----------------+
| browser   | count(browser) |
+-----------+----------------+
| Firefox 3 |             14 |
| Unknown   |             11 |
+-----------+----------------+

2 rows in set

3> select browser,count(browser) from logtest;

+-----------+----------------+
| browser   | count(browser) |
+-----------+----------------+
| Firefox 3 |             25 |
+-----------+----------------+

1 row in set

Why the query manner 1> and 2> result in the same result? Is there nothing difference between the count(*) and count(somefiled)?

Also,whay the query 2> and 3> result in the different result,why the groupby so magic? How does it work?

---

UPDATE: I am using MySQL5.1. :)

Answer 1

COUNT(*) is typically a shorthand for counting all records. Some RDBMS optimize it, like MySQL MyISAM tables. Otherwise, COUNT(column_name) is no different for non NULL values.

Your third query is different because you are still counting records, but because you are not grouping them by a column, you get the count for the all records. In your case not just Firefox 3 (14) and Unknown (11) which equals 25. The fact that is returns your top row is odd, and as other users noted should probably not work. Ultimately, this depends heavily on your RDBMS.

Not sure which RDBMS you are using, but you can read up on GROUP BY here. Generally speaking though they are most commonly used to support aggregate functions such as COUNT(), MAX(), AVG(), etc.

Answer 2

count(*) says to count all of the rows. count(browser) says to count all of the non-null browser values. This can make a significant difference, especially if you do not have an index on your browser column, as a table scan would then have to be performed.

The group by segments your count according to the column(s) specified.

Answer 3

COUNT(field) eliminates null values. SQL Server also supports some more flavors of that command, not sure if they're standard:

COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

As to your second question, groupby basically tells the aggregation operators to group their operation by a certain field. As you have noticed, if you don't specify a grouping field, they operate on the whole table. A little more from technet.

I'm not sure what's going on with result 3, as it doesn't look like valid SQL to me. Generally you can't mix aggregations like COUNT or AVG with non-aggregated fields unless you are grouping on those fields.

Answer 4

You must specify the SQL implementation or product you are using in order to get an accurate answer. Is this MySQL?

That said:

Queries 1 and 2 are almost the same. COUNT(*) will add one to the count for every row in the group. COUNT(FieldName) will add 1 every time the value in FieldName is not NULL. In your example, Browser is never NULL, so the result is the same.

When might it be different? If your table contained 10 rows with NULL in the browser field, the first query would have an additional line (NULL) | 10 and the second query would report (NULL) | 0.

The third query will not run in some SQL implementations. Because you're effectively "GROUPing BY" the entire table, how can the database figure out which value of Browser to put in the first column? It can't. Some implementations will not run this queries, others will select, more or less at random, a Browser value (which is what you're seeing).

Answer 5

A selection relationally gives you a result set. If you are grouping your selection by a field, the rows of the result set will be grouped by that field and each row of the result set will be specific for the group of the results.

For example you have a table named Animals with the following fields: Type, Gender, Name.

If you are running this query (in MySQL, for example):

select Type, Gender, Name from Animals where Type <> 'Pig'

you'll get all the animals which are not 'Pig'. If a row has Type = 'pig', it will be included into the results.

This query:

select Type, Gender, count(*) from Animals group by Type, Gender

will have this many rows: number of types * number of genders

You can make conditions for your group by's using the having clause in MySQL.

Read more here

The different between count(*) and count(browser) is that the first will return the number of all records, the second will return the number of all records where browser <> null.

Try inserting a row where browser = null and then run 1) and 2), this is the best test.