[twoquestions]

This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this?

A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.

[blattimwind]

Design queries iteratively.

Having an understanding of relational algebra (not the symbols, but the concept; math is always about the concept) generally helps a lot as well; it's the reason why compsci database lectures often start with relational algebra.

> We would like to find the total weight of cats grouped by age. But only return those groups with a total weight larger than 12.

The total weight of cats grouped by age.

    SELECT sum(weight), age
    FROM cats
    GROUP BY age

But only return those groups with a total weight larger than 12.

    SELECT sum(weight), age
    FROM cats
    GROUP BY age
    HAVING sum(weight) > 12

Ordered by age.

    SELECT sum(weight), age
    FROM cats
    GROUP BY age
    HAVING sum(weight) > 12
    ORDER BY age

The total weight column should be called total_weight.

    SELECT sum(weight) AS total_weight, age
    FROM cats
    GROUP BY age
    HAVING sum(weight) > 12
    ORDER BY age

[AlisdairO]

(self plug) try pgexercises.com. It's in a very similar vein to windowfunctions - I understand it was one of the original inspirations - but it covers a much wider range of material, with a gradual growth from very basic stuff up to slightly advanced.

[mercer]

I'll vouch for this plug! I've spent quite a bit of time learning SQL (and Postgres specific stuff), and this was one of my favorite resources.

I can also recommend Learning SQL by Alan Beaulieu, which was recommended in some HN comment. After looking into various books, some of them Postgres specific, I found this one to be the best for learning from scratch.

[ktr]

My introduction to window functions (and the best write-up I’ve seen) was through the SQL Cookbook (http://shop.oreilly.com/product/9780596009762.do). I highly recommend.

[AlisdairO]

This is a fantastic book - the name doesn't do it justice.

[emmelaich]

Try Joe Celko's book "SQL for Smarties" or ...

https://www.red-gate.com/simple-talk/sql/t-sql-programming/w...

[westurner]

Ibis uses windowing functions for aggregations if the database supports them. IDK when support for the new SQLite support will be implemented? http://docs.ibis-project.org/sql.html#window-functions

[EDIT]

I created an issue for this here: https://github.com/ibis-project/ibis/issues/1597

[btilly]

The discussion at https://news.ycombinator.com/item?id=17231349 has many more links and descriptions of how to use window functions in practice. (Including my comment at the top about how in practice I find that you usually want to combine window functions with nested queries.)

[twoquestions]

Thank you everyone for your help!