SELECT , COUNT() in SQLite

Question

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

score 28 · Accepted Answer · answered Sep 01 '10 at 06:55

28

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

answered Sep 01 '10 at 06:55

nos

223,662
58
417
506

2

SQLite help on aggregate functions (sqlite.org/lang_aggfunc.html) don't have anything about 'group by' :(. Is it some documentation i can read about this restriction for better understanding? – grigoryvp Sep 01 '10 at 07:08
2

@Eye of Hell any book on SQL would do, it's nothing sqlite specific. – nos Sep 01 '10 at 08:10
2

this answer lead me to this page of the SQLite documentation on resultsets, including GROUP BY: https://www.sqlite.org/lang_select.html#resultset – devnul3 Aug 06 '15 at 16:31

score 19 · Answer 2 · answered Apr 16 '15 at 02:25

19

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

    SELECT COUNT(*) FROM your_table;

answered Apr 16 '15 at 02:25

rafaelbattesti

562
4
13

score 10 · Answer 3 · edited May 23 '17 at 12:02

10

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

edited May 23 '17 at 12:02

Community

1
1

answered Sep 01 '10 at 06:57

Burcin

973
1
9
25

1

SQLite help on aggregate functions (http://www.sqlite.org/lang_aggfunc.html) don't have anything about 'group by' :(. Is it some documentation i can read about this restriction for better understanding? – grigoryvp Sep 01 '10 at 07:06
The SQLite documentation does say "in a group" / "in the group". I suggest to read a tutorial or a book about SQL. – Thomas Mueller Sep 01 '10 at 14:41
1

@EyeofHell only count() is the aggregate function GROUP BY is regular a part of SELECT syntax. http://sqlite.org/lang_select.html – XTL Apr 04 '12 at 07:35

score 5 · Answer 4 · edited Jul 24 '19 at 18:30

5

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)

edited Jul 24 '19 at 18:30

halfer

19,824
17
99
186

answered Apr 16 '15 at 02:33

Bob Jarvis - Слава Україні

48,992
9
77
110

SELECT , COUNT() in SQLite

4 Answers4

Linked

SELECT *, COUNT(*) in SQLite

4 Answers4

Linked

SELECT , COUNT() in SQLite