Forget PRQL, just use ChatGPT :-)

[richb-hanover]

(Just kidding...) But in a one-out-of one test, ChatGPT generated a valid SQL query...

Prompt: write a sql query to find all "BAR" in column named "Fred" from the table named Foo

Completion: SELECT * FROM Foo WHERE Fred = 'BAR';

You need an OpenAI account to look at the playground.

https://beta.openai.com/playground/p/4InXRr9QwfjHbkSOMy7vXDcp?model=text-davinci-003

[richb-hanover]

As I horse around with ChatGPT/OpenAI, I realize that it's way more powerful than I suspected. But it still has a way to go.

I found Klavier.ai that purports to suck up documentation and answer questions about it. I entered the link to the Language Book and it took a couple seconds before it was ready for a prompt. I gave it: how can i use aggregate in prql

The answer is so close, but so far off...

It seems to have cribbed the examples from the Aggregate page, and deduces that the result of the first query would have four columns (I don't think that's mentioned there.) But it also substituted some other "knowledge about SQL" to give wrong answers:

• the aggregate lines they should have been avg_salary = average salary,
• it used having... instead of the PRQL filter statement at the end

There's no action required from the PRQL team, but this stuff is really fascinating. Here's the full output...

In PRQL, you can use the aggregate transform to reduce each group of rows to a single row. The aggregate transform takes a list of aggregation functions, such as sum, avg, min, max, and count, and applies them to each group of rows defined by the group transform.

Here's an example of using aggregate in PRQL:

from employees
group [title, country] (
  aggregate [
    avg(salary) as avg_salary,
    sum(salary) as total_salary
  ]
)

In this example, we're grouping the rows by title and country, and then using the aggregate transform to calculate the average and total salary for each group. The resulting output will have one row for each group, with columns for title, country, avg_salary, and total_salary.

Note that you can also use the having transform to filter the groups based on the aggregated values. For example:

from employees
group [title, country] (
  aggregate [
    avg(salary) as avg_salary,
    sum(salary) as total_salary
  ]
  having total_salary > 100000
)

In this example, we're only including groups where the total salary is greater than 100,000.