4 ms·

Which RDBMS? This would not work on Oracle. You would need "group by WEEKDAY(created_at)" in this example.

• jcampbell1 13y ago
  MySQL and Postgres. You can use the source tables, anything in the select statement, or an ordinal position in the select statement. In the example "GROUP BY 1", "GROUP BY wkday", and "GROUP BY WEEKDAY(created_at)" are all the same.
  • lukaseder 13y ago
    Aagh, it works indeed in PostgreSQL: http://sqlfiddle.com/#!12/d41d8/1585 http://sqlfiddle.com/#!12/d41d8/1585 WITH orders(created_at) AS ( SELECT date '2012-01-01' UNION ALL SELECT date '2012-01-02' UNION ALL SELECT date '2012-01-03' UNION ALL SELECT date '2012-01-04' UNION ALL SELECT date '2012-01-05' UNION ALL SELECT date '2012-01-06' UNION ALL SELECT date '2012-01-07' UNION ALL SELECT date '2012-01-08' ) SELECT EXTRACT(DOW FROM created_at) wkday, COUNT(1) FROM orders GROUP BY wkday That's really disturbing. I fixed the linked article accordingly.
    • jcampbell1 13y ago
      You fixed the article incorrectly. SELECT aliases are never available in a WHERE clause. Edit: it seems more complicated that I initially assumed. MySQL doesn't allow it. SQLite does.
      • lukaseder 13y ago
        You're right, the new wording was ambiguous. A better wording is this: Rule number 2, for instance, does not apply exactly in the above way to MySQL, PostgreSQL, and SQLite.