In the preceding pages I've used some SQL statements without really explaining them. I guessed that you could figure out the purpose and syntax from the context and examples. However, here's a little more information on the GROUP BY (and HAVING ), ORDER BY , and WHERE clauses. GROUP BYHere's the important thing to remember: When you want to combine a group function (such as COUNT , AVG , or SUM ) with an individual function such as SELECT L_NAME (with L_NAME being an individual item), you have to use the GROUP BY statement. Otherwise, get ready for "ORA-xxx" errors that say some-thing about "not a group function." And here's one more important syntax item: SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY GROUP BY TEST_ID, SCORE
How do we use this feature? Suppose you have to produce a list of student scores by test, but you do not want to repeat duplicate scores. Running the preceding SELECT command will produce the following results:
Now suppose you wanted to list only the failing scores. Now you use the HAVING command, like this: SQL> SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY GROUP BY TEST_ID, score HAVING score < 70;
Here are the results:
Remember that HAVING is a group function and works only when you're using the GROUP BY command! Here's the error you get if you go after some-thing with the HAVING statement that is not in your GROUP BY clause: 1 SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY GROUP BY TEST_ID 2* HAVING score < 70 HAVING score < 70 * ERROR at line 2: ORA-00979: not a GROUP BY expression
ORDER BYNow let's add the ORDER BY function. As you've seen, the GROUP BY clause is used to group things together. It does not change the way items sort . Suppose you now needed to be able to list the scores in descending order by test ID. Here's where you add the ORDER BY statement, and notice that you can have more than one item in it: SQL> SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY GROUP BY TEST_ID, SCORE HAVING score > 70 ORDER BY TEST_ID, SCORE Here are the results: [3]
WHEREFinally, we have the WHERE clause. Think of WHERE as a gate or filter. It qualifies the rows that are returned, as in these examples: WHERE SCORE > 70 or WHERE TEST_ID = 1 . One of the most important uses of the WHERE clause is to prevent what is called a Cartesian product , which is just a way of saying that you can end up with huge results if you join tables and do not use a WHERE clause. For example, if we wrote a SELECT statement that selected all rows from STUDENTS and all rows from TEST_ID, and there were 500 rows in the STUDENTS table and 50 rows in the TEST_ID table, we would end up with 25,000 rows of rather useless data. (For you skeptics, go ahead and run Select * from students, test_id , and you will see that you will get back not the sum of the rows in both tables, but the result of multiplying the numbers of both tables! Do it now and get it out of your system. Do not do it when your systems are in production, or you'll bring them to their knees.) But beware; it happens. So use the WHERE clause whenever possible to prevent such situations, as well as to produce meaningful results. You can also use various operators with the WHERE clause, such as:
|