search

longde123 / csharp-sqlite

Automatically exported from code.google.com/p/csharp-sqlite
Other
0 stars 0 forks source link

Count in subquery #152

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
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 shakirov...@gmail.com on 13 Apr 2012 at 1:13

GoogleCodeExporter commented 9 years ago 
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 noah.hart@gmail.com on 29 Jun 2012 at 10:20

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
DB without data:
http://sdrv.ms/N3O5qV

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

Original comment by shakirov...@gmail.com on 2 Jul 2012 at 10:00

GoogleCodeExporter commented 9 years ago 
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 dilhan.j...@gmail.com on 27 Nov 2014 at 11:00

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
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 dilhan.j...@gmail.com on 28 Nov 2014 at 12:34