CODEWITHSUNDEEP
sqlpostgresqlgroup-bycount
Maggie Liu
Maggie Liu

Reputation: 366

COUNT Function: Result of a query

I'm having trouble with a part of the following question. Thank you in advance for your help. I have a hard time visualizing this "fake" database table. I was hoping someone could help me run through my logic and see if it's correct. If someone could just point me in the right direction that would be great!

About:

Sesame is a way to find online class for adults & activities for adults around you.

Imagine a database table named activities. It has four columns:

Question: Given the following query, which counts would you expect to return the highest and lowest values? Which counts would you expect to be the same? Why?

select
     
    count(activity_id),
    
    count(distinct activity_provider_id),
    
    count(area_id),     
    count(distinct area_id),
    count(*)

    from activities

My Solution

Highest values: count(*)


Reasoning: The Count(*) function returns the number of rows returned by a SELECT statement, including NULL and duplicates.

Lowest values: count(distinct activity_provider_id)

Reasoning: Less activity providers per activity per area*

Same: Unsure - Could someone just point me in the right direction?

Upvotes: 0

Views: 73

Answers (1)

GMB
GMB

Reputation: 222422

count(*) takes in account all rows in the table, while count(some_col) only counts non-null values of some_col.

Since activity_id is a non nullable colum, one would expect the following expressions return the same, "highest" count:

count(activity_id)
count(*)

As for wich expression returns the lowest count out of the three remaining choices, it is not really possible to tell for sure from the information provided in the question. If actually depends whether that are more, or less, distinct areas than activity providers.

There even is an edge case where all expressions return the same case, if all activity providers (resp. areas) are not null and unique in the table.

Upvotes: 1

Related Questions