CODEWITHSUNDEEP
sqldatabase
Sun Maung Oo
Sun Maung Oo

Reputation: 183

COUNT() return 0 on certain column

Requirements:I wanted to know how many images that each person have posted.

Therefore I create a table schema as follow.

Table=Person
==========
Id (PK) , Column1, Column2, LId (FK)

Table=ListMaster
============
Id (PK) , LId (Unique)

Table = ListDetail
===========
Id (PK), LId(FK), DataId(FK)

Table = Image
=========
Id (PK), Column1, Column2

and use a query SQL

SELECT  Person.Id AS PersonId,
        Person.Column1 AS PersonC1,
        Person.Column2 AS PersonC2, 
        COUNT(Image.Id) AS ImageCount
FROM Person
LEFT OUTER JOIN ListMaster ON Person.LId = ListMaster.LId
LEFT OUTER JOIN ListDetail ON ListDetail.LId = ListMaster.LId
LEFT OUTER JOIN Data AS Image ON ListDetail.DataId = Image.Id
GROUP BY Person.Id,
         Person.Column1,
         Person.Column2

I notice some person's "ImageCount" have "0" although there are images that he have posted before.

Could you please advise me how to fix my query or tell me it is even logically possible to do what I wanted? I suspect I make a mistake regarding my table design.

Sample Data
=======

Table = Person

Id (PK)(bigint identity) | Column1 (nvarchar(max)) | Column2 (nvarchar(max)) | LId (FK) (bigint [null])

1  |  Test 1 C1 | Test1 C2  | 1

2  |  Test 2 C1 | Test 2 C2 | 2

3 |   Test 3 C1 | Test 3 C2  | NULL

4 |   Test 4 C1 | Test 4 C4 |  37

Table = ListMaster

Id (PK)(bigint)(identity) | LId (Unique)(bigint)

1  |  1

2  |  2

3  |  37

Table = ListDetail



  Id (PK)(bigint identity)| LId(FK)(bigint not null)| DataId(FK)(bigint not null)
1  |  1 |  1

2  |  1 |  2

3  |  2 |  3

4  |  37 | 4

Table = Image

Id (PK)(big int not null)(identity) | Column1 (nvarchar(max)) | Column2 (nvarchar(max))

1 | Location 1  | Dummy Data 1

2 | Location 2  | Dummy Data 2

3 | Location 3 |  Dummy Data 3

4 | Location 4  |  Dummy Data 4

I expect the COUNT(Image.Id) AS ImageCount should return

2
1
0
1

but it return

2
1
0
0

EDIT 1 : Change the table design

EDIT 2 : Add a sample data

Upvotes: 0

Views: 127

Answers (3)

Jay
Jay

Reputation: 27474

It looks like your query and data as given should return the values you expect. Is this the actual schema, data, and query, or are you simplifying to make this post? I presume your real data does not include values like "test 1 C1", etc. Did you create a DB with these dummy field names and values to do this test, or are you saying that this is the equivalent of what you really have? If this isn't the actual stuff, it could well be that in simplifying you have left out the thing that's really causing the problem.

When I have a query that does not give the expected results, I try to drop out parts of the query to see where the problem is. Like try with only the first join and see if you get the expected results. If that works, add in the second join, etc. Leave out the GROUP BY and just dump all the records, so you can see the actual records and not just the count.

There are many possible sources of trouble. Maybe the data isn't what you think it is. Maybe one of the joins is using the wrong field. Maybe you're getting in trouble because you have different data types and a conversion is not giving the results you expect. Etc.

Upvotes: 1

Matt Gibson
Matt Gibson

Reputation: 38238

If, as you say, your Image.Id column is declared NOT NULL, the only reason you should get a COUNT(Image.Id) of 0 for a Person would be if your LEFT JOINs do not find any Images for a given Person. In that case, Image.Id in your underlying results will be NULL, and therefore COUNT(Image.id) will be zero. This means that either:

  • There's a Person who doesn't have any ListMaster entries.
  • There's a ListMaster which doesn't have any ListDetail entries.
  • There's a ListDetail entry which doesn't have any Data entries.

...or some combination of the above.

You should be able to quickly check which links are missing by adding COUNTs for the appropriate tables to your existing query:

SELECT  Person.Id AS PersonId,
        Person.Column1 AS PersonC1,
        Person.Column2 AS PersonC2, 

        -- NEXT TWO COUNTS ADDED FOR DEBUGGING
        COUNT(ListMaster.LId) AS ListMasterCount,
        COUNT(ListDetail.LId) AS ListDetailCount,

        COUNT(Image.Id) AS ImageCount
FROM Person
LEFT OUTER JOIN ListMaster ON Person.LId = ListMaster.LId
LEFT OUTER JOIN ListDetail ON ListDetail.LId = ListMaster.LId
LEFT OUTER JOIN Data AS Image ON ListDetail.DataId = Image.Id
GROUP BY Person.Id,
         Person.Column1,
         Person.Column2

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Try this to check if all your Id, Column1, Column2 have image

SELECT  Person.Id AS PersonId,
        Person.Column1 AS PersonC1,
        Person.Column2 AS PersonC2, 
        ListMaster.*,
        ListDetail.*,
        Data.*
FROM Person
LEFT OUTER JOIN ListMaster ON Person.LId = ListMaster.LId
LEFT OUTER JOIN ListDetail ON ListDetail.LId = ListMaster.LId
LEFT OUTER JOIN Data AS Image ON ListDetail.DataId = Image.Id
ORDER BY Person.Id,
         Person.Column1,
         Person.Column2

Upvotes: 0

Related Questions