Day 030 - Sorting, counting, and grouping in queries

Owned by David Harms
Last updated: Apr 26, 2013Version comment
4 min read

Continuing on with queries, I played around a bit with sort orders. The tutorial only told me to sort on one field, but how often does that happen? So I added a second sorted field. And that made me wonder how I would go about changing the order of those fields, i.e. switch between ZipCode/SupplierName and SupplierName/ZipCode.

You can't do this by moving the fields up and down in the list (the move buttons are hidden by the popup menu). All that does is change the order in which fields are returned by the query. You can deselect and reselect the sort fields, but that's tedious. Instead, click on the Sort button:

From this window it's easy to move sort fields around, add/remove sort fields and change sort direction. 

After validating all my settings (and reverting to what the tutorial told me to do) the IDE showed me a nice graphical representation of my query:

I saved the query and ran it:

Query results can be exported directly to Excel, XML and Word. 

A count query

The next step in the tutorial was to create a query that counted the number of instances of a value. I created a new query and added the City field from the Customer file. I then clicked on Count.

That isn't the kind of query I'd usually use - instead if I'm counting I often want a ranking, such as the cities with the most customers. 

In Postgres I'd write the query like this:

select city,count(city) as count from customer group by city order by count desc

That would give me a ranking of my cities starting with the city with the most customers. I thought maybe "Count the records with different values" would do the job, but that resulted in this code:

SELECT 
  COUNT(DISTINCT CUSTOMER.City) AS Count_1
FROM 
  CUSTOMER

That provides a total number of distinct cities which isn't the same thing. I poked around the query settings and didn't find a way to add a group by clause, but I gather you can still use this if you write the SQL query by hand. 

In the next tutorial step, however, I created a query that did generate a group by clause. 

I added the CustomerName and City fields from the Customer file and the GrandTotal field from the Orders file. I then added a Sum calculation on GrandTotal, and the query editor generated the following SQL:

SELECT 
  CUSTOMER.CustomerName AS CustomerName,  
  CUSTOMER.City AS City,  
  SUM(ORDERS.GrandTotal) AS sum_GrandTotal
FROM 
  CUSTOMER,  
  ORDERS
GROUP BY 
  CUSTOMER.CustomerName,  
  CUSTOMER.City

It inferred from the relationships that I needed a join, and it also inferred that I wanted to group by the customer name and city. Perhaps this logic only comes into play when joins are involved. 

Here's the graphic:

I'd still like to know how I can force a group by in the query editor. Perhaps the answer is in...

Query source

Lesson 6.4 on page 208 is about the SQL language, and queries are translated into SQL. For any given query you can view the "everyday language" description:

or the SQL description:

I've worked with SQL longer than I've worked with WinDev, so I know which I prefer. 

But this section of the tutorial didn't tell me anything new about how to manipulate queries; it just provided an overview of the core SQL language statements.

The tutorial points out that you can use queries as data sources for reports and controls. It also provides this example of using a query in hand code:

I'm still hoping there are more options available for manipulating the SQL used by queries, but I remain impressed by how easy it is to implement, test and reuse queries in WinDev.