SELECT *, COUNT(*) in SQLite

If i perform a standard query in SQLite:

SELECT * FROM my_table

I get all records in my table as expected. If i perform following query:

SELECT *, 1 FROM my_table

I get all records as expected with rightmost column holding '1' in all records. But if i perform the query:

SELECT *, COUNT(*) FROM my_table

I get only ONE 开发者_StackOverflow中文版row (with rightmost column is a correct count). Why is such results? I'm not very good in SQL, maybe such behavior is expected? It seems very strange and unlogical to me :(.

SELECT *, COUNT(*) FROM my_table is not what you want, and it's not really valid SQL, you have to group by all the columns that's not an aggregate.

You'd want something like

SELECT somecolumn,someothercolumn, COUNT(*) 
   FROM my_table 
GROUP BY somecolumn,someothercolumn

If you want to count the number of records in your table, simply run:

    SELECT COUNT(*) FROM your_table;

count(*) is an aggregate function. Aggregate functions need to be grouped for a meaningful results. You can read: count columns group by

If what you want is the total number of records in the table appended to each row you can do something like

SELECT *
  FROM my_table
  CROSS JOIN (SELECT COUNT(*) AS COUNT_OF_RECS_IN_MY_TABLE
                FROM MY_TABLE)