How to use COUNT to duplicate rows in postgres?

Question

I want to be able to create a histogram from a tuple containing two integer values. Using postgresql.

Here he is:

SELECT temp.ad_id, temp.distance as hits FROM ( 'UNION ALL .join(cupound_query)' # python ) as temp GROUP BY temp.ad_id,temp.distance 

EDIT : the best example of what I want

For this input:

 (51, 5) (51, 0) (51, 3) (51, 0) (88, 2) (88, 2) (88, 2) (84, 1) (81, 9) 

Will be:

  (88,2) : 3 (51,0) : 2 (51,3) : 1 (51,5) : 1 (84,1) : 1 (81,9) : 1 

How to create a histogram of these values? Put in another way, how can I calculate how many times a row has a duplicate?

thanks

Answer 1

Your question leaves room for interpretation. This test test shows two nested steps:

 CREATE TEMP TABLE x (ad_id int, distance int); INSERT INTO x VALUES (510, 0),(956, 3),(823, 3),(880, 2) ,(523, 3),(467, 0),(843, 1),(816, 9) ,(533, 4),(721, 7),(288, 3),(900, 3) ,(526, 9),(750, 7),(302, 8),(463, 6) ,(742, 8),(804, 2),(62, 7),(880, 2) ,(523, 3),(467, 0),(843, 1),(816, 9) ,(533, 4),(721, 7),(288, 3),(900, 3) ,(526, 9),(750, 7),(302, 8),(816, 9) ,(533, 4),(721, 7),(288, 3),(900, 3) ,(533, 4),(721, 7),(288, 3),(396, 5); 

How many duplicates per value?

 SELECT ad_id, count(*) AS ct FROM x GROUP BY 1; 

Result:

 ad_id | ct -------+---- 62 | 1 288 | 4 302 | 2 396 | 1 ... 

Read: ad_id 62 exists 1x, ad_id 288 exists 4 times, ...

---

"How can I calculate how many times a row has a duplicate?"

 SELECT ct ,COUNT (*) AS ct_ct FROM (SELECT ad_id, COUNT (*) AS ct FROM x GROUP BY 1) a GROUP BY 1 ORDER BY 1; 

Result:

  ct | ct_ct ----+--- 1 | 8 2 | 7 3 | 2 4 | 3 

Reading: 8 occurrences of " ad_id unique", 7 occurrences of "2 lines with the same ad_id ", ...

Answer 2

Just add count(*) to your selection:

 SELECT temp.ad_id, temp.distance as hits, count(*) .... 

Answer 3

The following is a tutorial in which I wrote about how to generate histograms directly in SQL using Postgres:

Simple Bar Charts in SQL

I think you could easily adapt this example to your table structure.