Use cid in sqlite instead of column name?

Question

When creating a view, the name assigned to a column is not always usable in sqlite, in order to perform a specific operation afterwards. For instance, I created the following view:

CREATE VIEW myview AS 
  SELECT amount, count(name), name 
    FROM mytable 
GROUP BY name 
  HAVING count(name) > 1;

Then I would like to do the following operation:

SELECT total(amount*count(name))
  FROM myview;

Unfortunately, count(name) is the name given by sqlite to the second column, hence, cannot be used in a select statement. the PRAGMA table_info(myview) shows us that the cid to count(name) is 1, is there anyway to use this information to be able to do some computation on that column?

Answer 1

You need to define a column alias:

CREATE VIEW myview AS 
  SELECT amount, 
         COUNT(name) AS name_count, 
         name 
    FROM mytable 
GROUP BY name 
  HAVING count(name) > 1;

Then, you'll be able to use:

SELECT SUM(v.amount * v.name_count)
  FROM myview v;

I changed out total for SUM, which is more consistent with other databases (SQLite aggregate functions).