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?

Answers


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 ║
╚═══╩════════════╝

Need Your Help

CakePHP 2.3.2 BasicAuthentication not working

cakephp basic-authentication cakephp-2.3

I tried out the "Simple Acl controlled Application 1&2" tutorial located at

Trouble with a Semantic UI/jQuery dropdown button using a block component in Ember JS

jquery ember.js semantic-ui

I'm having difficulty getting a Semantic UI (1.12.2) jQuery dropdown button located inside a block component to work in Ember JS (1.11.0). I think I understand the run loop issues with binding jQue...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.