How to Group and count in Postgresql

Question

I have a table like this

date        |  text | link_id
------------+-------+----------
 05-10-2014 | lorem |    2
 06-10-2014 | lorem |    4
 05-10-2014 | lorem |    4
 05-10-2014 | lorem |    5
 05-10-2014 | lorem |    5
 05-10-2014 | lorem |    5
 05-10-2014 | lorem |    5
 05-10-2014 | lorem |    5
 05-10-2014 | lorem |    6
 05-10-2014 | lorem |    4

I need to get the count of group from my rows here the result

    date    |  text | link_id  | value
------------+-------+----------+-------
 05-10-2014 | lorem |    2     |   1
 06-10-2014 | lorem |    4     |   3
 05-10-2014 | lorem |    5     |   6

and why if I use function

COUNT(DISTINCT n)

or only

COUNT()

it always forces me to add all SELECT statement to GROUP

and I'm only Distinct 1 row

ds27680 · Accepted Answer · 2014-10-05 18:38:49Z

1

Try:

Select link_id, Count (link_id)
From table
Group by link_id;

where you should replace table with the name of your table.

Aggregate functions get applied to all rows in a group. So you first group by link_id and get groups of rows with the same link_id. Count gets applied then to each group to compute the number of rows in each group. You would get then link_id && number of rows with same link_id as result.

Please note that it is actually unclear from your question how do you intend actually to select the data for the two other colums. In the example you given fot link_id 4 you expect to get count 2, however the dates for the two records with link_id 4 differ (one is 5.10.2014 the other is 6.10.2014). Which one should be taken for the result?

edited Oct 5, 2014 at 18:38

answered Oct 5, 2014 at 18:11

ds27680

1,99310 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Group and count in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related