Aggregate by distinct character PostgreSQL

Question

I have column zone_dist in my table parcel16 that contains land use codes (character). My objective is to create a two-column table which in the left-hand column shows all of the distinct values and in the right shows the total count of those values in the table, in descending order. I have tried with a basic query but cannot apply the sum function to a character value:

SELECT  zone_dist, SUM(zone_dist) AS quantity
FROM parcel16
GROUP BY zone_dist

returns the error:

ERROR:  function sum(character varying) does not exist
LINE 1: SELECT  zone_dist, SUM(zone_dist) AS quantity
                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How would one go about taking the counts of all distinct character values?

count don't sum. Sum wan'ts to do math on character data, count simply counts occurances of zone_dist in parcel16. — xQbert
– xQbert, Commented Dec 19, 2016 at 19:14

pizzaslice · Accepted Answer · 2016-12-19 19:15:16Z

1

You want Count() rather than Sum(). Sum() adds the aggregate values (assumes int) whereas Count() will count the number of those values in which you group on.

SELECT  zone_dist, count(zone_dist) AS quantity
FROM parcel16
GROUP BY zone_dist
order by count(zone_dist) desc

answered Dec 19, 2016 at 19:15

pizzaslice

4782 silver badges8 bronze badges

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

1 Comment

David דודו Markovitz Over a year ago

(1) If zone_dist can be NULL it won't be count (2) Can be simplified using the quantity alias for the ORDER BY

xQbert · Accepted Answer · 2016-12-19 19:15:28Z

0

Sum does math on string data.

Count simply increments by 1 for each occurrence of a zone_dist. (thus it ignores nulls)

SELECT  zone_dist, count(zone_dist) AS quantity
FROM parcel16
GROUP BY zone_dist

answered Dec 19, 2016 at 19:15

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Comments

David דודו Markovitz · Accepted Answer · 2016-12-19 21:00:03Z

0

If we have NULL values in column zone_dist, count(zone_dist) won't count them and instead return 0
The quantity alias can be used in the order by clause

select   zone_dist, count(*) as quantity 
from     parcel16 
group by zone_dist 
order by quantity desc

edited Dec 19, 2016 at 21:00

answered Dec 19, 2016 at 19:30

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Collectives™ on Stack Overflow

Aggregate by distinct character PostgreSQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related