COUNT query produces unintuitive results?


#1

I dont understand this: “SELECT COUNT(*) as count, relation FROM tutorial”

It returns:

{
“results”: [
{
“count”: 6,
“relation”: “friend”
}
]
}

To me intuitively the query says: “get the count for everything (which is obviously just a single result)” and then get the relation… which should be multiple different results.
What’s the trick to understand this?


#2

In this case, COUNT() essentially infers that you are grouping all values. If you like the output to include full list of relations you can use the following;
SELECT ARRAY_AGG(relation), COUNT(
) FROM tutorial


#3

Also, to get the count for each relation:

SELECT COUNT(*), relation
FROM tutorial
GROUP BY relation