SELECT *, COUNT(*) in SQLite

Question

Hello.

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 :(.

Answer 1

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

Answer 2

count(*) is an aggregate function. Aggregate functions need to be grouped for a meaningful results. You can read: http://stackoverflow.com/questions/2698108/count-columns-group-by