CODEWITHSUNDEEP
sqlsqliteselectcount
Hopeless
Hopeless

Reputation: 4773

Select count(*) doesn't return rows which have the results of 0 in SQLite?

Here is the sample table:

A      |     Column1      |      Column2
1               5                    8
1               3                    2
2               6                    9
2               2                    5
3               1                    3

Here is my query:

Select A,count(*) as C from myTable where Column1 > 3 group by A

The result should be:

A     |     C
1           1
2           1
3           0

But it gave me this result which excluded all 0 rows:

A     |     C
1           1
2           1

I want to include the rows which don't meet the condition in where clause.

Could I do to achieve that?

Upvotes: 1

Views: 517

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78513

Your where clause eliminates them. Try something like:

Select A,count(case when Column1 > 3 then 1 else null end) as C from myTable group by A

Upvotes: 1

John Woo
John Woo

Reputation: 263803

The WHERE clause causes the A = 3 not on the list because it filter the rows before the aggregation of the records.

SELECT  A,
        COUNT(CASE WHEN Column1 > 3 THEN 1 END) TotalCount
FROM    myTable
GROUP   BY A

OUTPUT

╔═══╦════════════╗
║ A ║ TotalCount ║
╠═══╬════════════╣
║ 1 ║          1 ║
║ 2 ║          1 ║
║ 3 ║          0 ║
╚═══╩════════════╝

Upvotes: 2

Related Questions