Re:Easy Steps to a Complete Understanding of SQL
by jcampbell1 on Tuesday September 03, 2013 @10:50AM
(#6321279)
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.
[ Parent
| on HN ]
Re:Easy Steps to a Complete Understanding of SQL
by lukaseder on Tuesday September 03, 2013 @11:03AM
(#6321351)
Aagh, it works indeed in PostgreSQL: 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.
Re:Easy Steps to a Complete Understanding of SQL
by jcampbell1 on Tuesday September 03, 2013 @11:40AM
(#6321554)
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.