CODEWITHSUNDEEP
mysqlsqlsyntaxcount
XilliOne
XilliOne

Reputation: 11

Understanding simple count logic in sql

I have a very basic question which I cannot answer myself but shouldn't take much of your time.

The following query works, it lists all the exhibition_category_id and counts the total of objects that are assigned to each category.

My question is: Why does it do it? I don't understand the query. It says count(*) - why doesn't it give me the total of different exhibition_category_id's (79), but instead counts, how many objects are assigned to each category?

Here is the query in question, as well as a screen shot from the actual output:

SELECT eb.exhibition_category_id, count(*) AS total 
FROM exhibition_brand eb 
GROUP BY eb.exhibition_category_id

https://i.sstatic.net/6deMv.png

Hope its understandable what I am asking for, eager to improve my post based on feedback. Cheers

Upvotes: 0

Views: 69

Answers (2)

circo
circo

Reputation: 166

The GROUP BY function groups the COUNT() by eb.exhibition_category_id, so the query groups the records by eb.exhibition_category_id, then counts the corresponding records.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Your query is a basic aggregation query:

SELECT eb.exhibition_category_id, count(*) AS total
FROM exhibition_brand eb
GROUP BY eb.exhibition_category_id;

The GROUP BY specifies that the result set will contain one row for each value of eb.exhibition_category_id. The result set consists of two columns, one is the value that defines the row. The other is a count of the number of rows in each group. That is what COUNT(*) does.

If you wanted the total count of different eb.exhibition_category_id, then you want one row and COUNT(DISTINCT):

select count(distinct eb.exhibition_category_id)
from exhibition_brand eb;

Upvotes: 1

Related Questions