0

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

1 Answer 1

2

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.

4
  • Yep. +1. Note that you don't need to place the columns in the select list, only in GROUP BY (of course, if you don't put them in the select list, you'll only have the counts as output. But if you do need only the 4,2,1,2 numbers in the example, say for some statistical analysis, then it's perfectly ok to not have col1,col2,col3 in the select list ). Jul 27, 2022 at 10:25
  • I think postgres has this contraint. Not sure a practical demonstration will help. Jul 27, 2022 at 11:07
  • No, no DBMS has this constraint Jul 27, 2022 at 13:29
  • select count(*) as count from Mytable GROUP BY col1,col2,col3; works fine, in all DBMS. You are probably thinking of the reverse, using a column in the select without having in the GROUP BY: select col1,col2,col3,count(*) as count from Mytable ; That will not work in Postgres and in most DBMS. Jul 27, 2022 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.