[sql] 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 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 :(.

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

    SELECT COUNT(*) FROM your_table;

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)

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