StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As <a href="https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by">is the case with most relational database systems</a>, the <code>SELECT</code> clause is processed after the <code>GROUP BY</code> clause. This means you cannot use columns aliased in the <code>SELECT</code> (such as <code>ts</code> in this example) in your <code>GROUP BY</code>.</p> <p>There are essentially two ways around this. Both are correct, but some people have preference for one over the other for various reasons.</p> <p>First, you could group by the original expression, rather than the alias. This results in duplicate code, as you will have the exact same expression in both your <code>SELECT</code> and <code>GROUP BY</code> clause.</p> <pre><code>SELECT FROM_UNIXTIME(unix_timestamp(time,'yyyy-mm-dd hh:mm:ss'),'yyyy-mm-dd hh:mm') as ts, COUNT(*) as cnt FROM toucher GROUP BY FROM_UNIXTIME(unix_timestamp(time,'yyyy-mm-dd hh:mm:ss'),'yyyy-mm-dd hh:mm') LIMIT 10; </code></pre> <p>A second approach is to wrap your expression and alias in a subquery. This means you do not have to duplicate your expression, but you will have two nested queries and this may have performance implications.</p> <pre><code>SELECT ts, COUNT(*) as cnt FROM (SELECT FROM_UNIXTIME(unix_timestamp(time,'yyyy-mm-dd hh:mm:ss'),'yyyy-mm-dd hh:mm') as ts, FROM toucher) x GROUP BY x.ts LIMIT 10; </code></pre> <p>Both should have the same result. Which you should use in this case will depend on your particular use; or perhaps personal preference.</p> <p>Hope that helps.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload