Sqlite: Alias Column Name Can't Contains a Dot "."

SQLite Select data where the column name contains a string?

Since SQLite 3.16, you can use PRAGMAs in queries:

SELECT name
FROM pragma_table_info('MyTable')
WHERE name LIKE ...

How to do arithemtic operations with an alias in sqlite

You don't even need a subquery here:

SELECT
    a.albumid,
    a.title,
    COUNT(t.albumid) AS tracks_count,
    COUNT(t.albumid) * a.album_nr AS other_count
FROM albums a
LEFT JOIN tracks t
    ON a.albumid = t.albumid
GROUP BY
    a.albumid,
    a.title;

If you wanted to make your current approach work, then the problem you are having is that you are referring to the tracks_count alias in the same select in which it was defined. This isn't allowed, because the alias may not have even been computed yet. But, I would recommend using the answer I gave above.

SQLite: Table alias not found when trying to create a new table from a join of two select statements

Wrapping your queries in SELECT * FROM (...) is causing the problem, because you can't refer to aliases in a subquery.

SELECT t1.story_id, t1.role_type, t1.user_id, t2.workstream_num
FROM (
    SELECT story_id, role_type, user_id 
    FROM cur_cycle_role_activity) AS t1
LEFT JOIN (
    SELECT story_id, workstream_num
    FROM cur_cycle_role_activity) AS t2
ON t1.story_id = t2.story_id

Are there any restrictions on Postgres column alias names?

The rules for a column alias are no different to than those for regular column names.

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Key words and unquoted identifiers are case insensitive.

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). Quoted identifiers can contain any character, except the character with code zero.

---

Related Topics