sql - Counting how many times each value occurs in a PostgreSQL table? -

sql - Counting how many times each value occurs in a PostgreSQL table? -

so have table 3 of import columns: store location, customer, , number of purchases. like:

store | client | number of purchases larry 2 b moe 4 c curly 7 b tina 1 dina 6 c archer 12 d mallory 3

what count of each number of purchases. is, count number of times customers made 1 purchase, 2 purchases, 3 purchases, 4 purchases etc. kind of histogram, grouped store.

store | 1 purchase | 2 purchases | 3 purchases... 1 3 2 b 2 1 4 c 1 6 8 d 4 4 2

is there clever way without manually finding out maximum number of purchases , creating branched count count each 1 of those? have

select store, count(case number_of_purchases when 1 1 else null end) 1_purchase, count(case number_of_purchases when 2 1 else null end) 2_purchase, count(case number_of_purchases when 3 1 else null end) 3_purchase... table grouping store;

but, since max number can alter on time, i'd query calculate automatically , take account. help appreciated!

to right data, need group by , aggregate function.

select store, number_of_purchases, count(number_of_purchases) table1 grouping store, number_of_purchases order store, number_of_purchases;

for format, you'll need utilize 1 of crosstab() functions in tablefunc extension. along these lines.

select * crosstab('select store, number_of_purchases, count(number_of_purchases) table1 grouping store, number_of_purchases order 1, 2', 'select n generate_series(1, 12) n order 1') (store text, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int, "10" int, "11" int, "12" int) ;

personally, don't crosstabs kind of data. end output that's hundreds or thousands of columns wide, of "cells" empty.

sql postgresql pivot