The convenient way of thinking about the query clauses is that they are processed sequentially from the top down. First, the SELECT clause tells the query what columns are to be in the resulting recordset. Then, the FROM clause tells the query to take the records from the Products tabular section across all Sales documents. Then the JOINs grab information from other tables: the Sales documents table and the Products catalog. Then it’s the WHERE clause’s turn. It looks at the conditions we gave and filters out every record that doesn’t meet them. Now let’s look at the next two clauses down.
So this is what SELECT, FROM and WHERE clauses output for us: a list of products sold across all my Sales documents. Obviously, the same products appear more than once in the list. Is there a way to modify this query so it sums up the quantities and prices for each product, like this? Well, this is exactly what the GROUP BY clause does.
First of all, I have to tell the query what field (or fields) to group the results by. What I said here is basically this hey, query, combine all records having the same Product field value into a single record. But if I try to run the query, this is what I get. The query says that it cannot group the results by Products and output the Document number at the same time. The problem here is that there are records with the same product belonging to the different documents. So the query just doesn’t know what document number to take. The only field it knows how to output now is the Product field. As for the rest of them, we need either to remove the field from the output list or tell the query how to aggregate all its different values into one.
Let me show you what we can use aggregation-wise. As you can see we have a whole bunch of functions to aggregate numeric values: sum them up, calculate the average value, take the minimum or maximum and count them up. None of them (for the exception of the COUNT, probably) makes much of a sense for string values, although we still can use MIN and MAX, because the Platform can compare strings, as you remember.
So, let me just delete the Document and its JOIN like this. And now we have the same problem with the ProductType field. So, I’m going to delete this field from the output but I still need this JOIN because of this condition over here, so I’m keeping it. And now I am going to sum up the Quantity and Line Total like this. And here we go. This is the result I needed.
Alright. Now to the HAVING clause. You can think of HAVING as of WHERE that works after the grouping is done and only for the grouped fields. For example, I can leave in the output only the records with the Price over 40 dollars. And here we go. Or I can select only products containing "Coca" substring anywhere in the description like this. Check out, by the way, this LIKE logical expression. These percent wildcards mean any number of any symbols, so this condition found both cokes we had. So, these were our GROUP BY and HAVING clauses.
One last thing that doesn’t deserve its own episode but is worth mentioning anyway: the ORDER BY clause. This is a simple one. Just list all the fields you want your output to be sorted by and here you go. And that’s pretty much it.