Count in subquery

[GoogleCodeExporter]

In Silverlight project query:

SELECT
    [n].[NewsId],
    (
        SELECT
            Count(*)
        FROM
            [NewsComment] [c]
        WHERE
            [n].[NewsId] = [c].[NewsFeedId]
    ) as [c1]
FROM
    [NewsFeed] [n]

return wrong count values on cloumn c1.

But Query

SELECT
    [n].[NewsId],
    (
        SELECT
            Sum([c].[NewsFeedId])
        FROM
            [NewsComment] [c]
        WHERE
            [n].[NewsId] = [c].[NewsFeedId]
    ) as [c2]
FROM
    [NewsFeed] [n]

return correct Sum.

Original issue reported on code.google.com by [email protected] on 13 Apr 2012 at 1:13

[GoogleCodeExporter]

May be a SQLite issue, some of the aggregate functions were changed after 3.7

Can you supply your schema and some sample data please

Original comment by [email protected] on 29 Jun 2012 at 10:20

• Changed state: Reviewed

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

DB without data:
http://sdrv.ms/N3O5qV

I don't have any tools to generate sample data.

Original comment by [email protected] on 2 Jul 2012 at 10:00

[GoogleCodeExporter]

I am having the same issue. COUNT is not working properly if it is inside a Sub 
query and the sub query uses outer table column to filter data

select (select count(*) from Cars where Cars.TypeID = CarTypes.TypeID) AS 
CarTypeCount
from CarTypes

Original comment by [email protected] on 27 Nov 2014 at 11:00

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I found a Solutions for this issue,

just use  SUM(1)  instead of  COUNT(*) in your query

Because sum(1) is exactly the same as count(*) 

to work this properly Use following complete query,

IFNULL(SUM(1),0) AS MY_COUNT

Original comment by [email protected] on 28 Nov 2014 at 12:34