CODEWITHSUNDEEP
sqlpostgresqlpivot
user2900369
user2900369

Reputation: 809

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

So I have a table with three important columns: Store location, customer, and number of purchases. Something like:

Store   |   Customer   |   Number of purchases
A           Larry          2
B           Moe            4
C           Curly          7
B           Tina           1
A           Dina           6
C           Archer         12
D           Mallory        3

What I would like to do is get a count of each number of purchases. That is, count the number of times customers made 1 purchase, 2 purchases, 3 purchases, 4 purchases etc. kind of like a histogram, grouped by store.

Store   |   1 Purchase   |   2 Purchases   |   3 Purchases...
A           1                3                 2
B           2                1                 4
C           1                6                 8
D           4                4                 2

Is there any clever way to do this without manually finding out what the maximum number of purchases is and creating a branched count to count each one of those? So I already have 

SELECT Store,
      Count(CASE number_of_purchases when 1 then 1 else null end) as 1_purchase,
      Count(CASE number_of_purchases when 2 then 1 else null end) as 2_purchase,
      Count(CASE number_of_purchases when 3 then 1 else null end) as 3_purchase...
FROM table
GROUP BY Store;

But, since the max number can change over time, I'd like the query to calculate it automatically and take that into account. Any help would be appreciated!

Upvotes: 0

Views: 4991

Answers (2)

gmarintes
gmarintes

Reputation: 1308

Try this:

SELECT
    Store, number_of_purchases, COUNT(DISTINCT number_of_purchases) AS cnt
FROM table
GROUP BY Store, number_of_purchases

The result will be arranged in rows (not in columns) because you don't know what is the max number of purchases for each Store.

It will be easy to loop against the result since they will be ordered by Store and number_of_purchases.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

To get the right data, all you need is group by and an aggregate function.

select store, number_of_purchases, count(number_of_purchases)
from Table1
group by store, number_of_purchases
order by store, number_of_purchases;

For the format, you'll need to use one of the crosstab() functions in the tablefunc extension. Something along these lines.

select * 
from crosstab('select store, number_of_purchases, count(number_of_purchases)
               from Table1
               group by store, number_of_purchases
               order by 1, 2', 
              'select n from generate_series(1, 12) n order by 1') 
     as (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, I don't like crosstabs for this kind of data. You could end up with output that's hundreds or thousands of columns wide, with most of the "cells" empty.

Upvotes: 1

Related Questions