Count how many times a combination of columns occur

Question

I have the following table:

Mytable:
-------
col1 INT
col2 INT
col3 INT

And in postgresql I want to count the number of occurences that a combination of values in col1, col2, col3 occur.

For example if the Mytable has the values:

col1	col2	col3
1	5	3
1	8	3
1	5	3
1	5	3
1	5	3
1	5	4
1	5	4
2	5	3
1	8	3

How I can generate the following result:

col1	col2	col3	count
1	5	3	4
1	8	3	2
2	5	3	1
1	5	4	2

Answer 1

GROUP BY is that you want. GROUP BY allows you to group some columns together in case you want to aggregate some data.

Aggregation does happen when you use:

1. Arithmetic operation sun as SUM() or AVG()
2. Counting using COUNT()
3. etc etc

In your example the SQL you need is:

select col1,col2,col3,count(*) as count from Mytable GROUP BY col1,col2,col3;

With that we tell to postgresql server to:

1. Count DATA
2. But for each different values of columns col1,col2,col3

The GROUP BY is used to distinguish - generate different groups using the values of the columns provided after that.

So in your example the combination of values (1,5,3) of col1,col2,col3 respectively is a single group.

Keep in mind that you need to place the columns you need to group both in Select portion of the query and in the group by portion of it as well.