Group By

Mysql is a very nice database. Just like php its extreamly forgiving. Anyone with experience with RDBMS setups will know exactly what we mean. For this article, we will be discussing the “Group By” argument and how mysql makes really good guesses. Moreover, we will be discussing what happens when mysql doesn’t make that guess and you have to learn a little more SQL to generate the results

Scenario

We were looking for the “COUNT” or amount of times, each distinct record in a particular column existed. So in a column of types, what types exist and how many of said type. The type should not be repeated within the result. Under a simple test, we can see that there is absolutely no need for a “Group By” clause

[php]

mysql_query("SELECT TYPE , COUNT( id ) AS amt
FROM equiptable1");

[/php]

This will produce a result with one row. The issue here is that there are too many columns  for mysql to figure out the important aspects. The truth is, in any RDBMS, that query probably won’t work without the “Group By” clause once it gets anymore complex like the following join example. Thats right, you have been ignoring its use because mysql has been helping you. Just like php but thats a topic for another article.

The Solution

Group by is your friend. Take a look at the following query.  It would return one result without a “Group by” within the clause, The simple answer is the following;

[php]

mysql_query("SELECT TYPE , COUNT( id ) AS amt
FROM equiptable1
GROUP BY TYPE HAVING amt>1");

[/php]

Rabbit Hole Depth

Now lets consider the scenario that probably helped you to stumble across this article. You are trying to jump across several tables like a super star, and you need specific results from one. Eurica, now you have the power of the “Group BY” clause, to explain to the database exactly what the focus of your query is. Take a look at the following example

[php]

mysql_query("SELECT equiptable.Type , COUNT( equiptable.id ) AS amt FROM site1table1 as site
left join bp_groups as ggg ON ggg.id = site.group_id
left join equiptable1 as equiptable ON equiptable.SiteNo = site.SiteNo
WHERE site.group_id = ggg.id AND equiptable.id IS NOT NULL AND ggg.id = ".$bp->groups->current_group->id AND amt > 1 ."GROUP BY equiptable.Type");

[/php]

As you can see, we took this code right out of a real installation. We believe in using real world examples, that way you can visualize actually doing this. The point of the example is that the equiptable1 holds the info yet we need to jump across bp_groups and site1table1 to get there.

Conclusion

The point to take away is that the group by clause becomes most useful when you are engaging in multiple joins. What not covered here is how this setups you up for advanced concepts like group_concat().  However, the simple fact is that if you are trying to get details of specific columns, you may not be retreiving the right result because of the lack of “Group By”.