CODEWITHSUNDEEP
sqlpostgresql
John Sly
John Sly

Reputation: 769

Why does my query involving division and COUNT always result in 1?

I've simplified this down a bit since the literal data is pretty massive but a very simple example will suffice. I'm working on a query where because of the massive amount of data, I'm looking to do some aggregation in one shot instead of many many steps.

I have two tables

<<customers>>
id | first_name | last_name
1  | Reed       | Richards
2  | Johnny     | Storm
3  | Peter      | Parker

<<purchases>>
id | cid | date
1  | 1   | 2017-01-09
2  | 2   | 2017-01-09
3  | 2   | 2017-01-09
4  | 3   | 2017-01-09

When I run the query

SELECT 
    COUNT(c.id) as "Total Customers",
    COUNT(p.id) as "Total Sales",
    COUNT(c.id)/COUNT(p.id) as "Sales per customer"
FROM test_customers c
    LEFT OUTER JOIN test_purchases p ON c.id = p.cid

I get 

4 | 4 | 1

When I'm looking for ...

3 | 4 | 1.3333333...

This example is extremely simplified, but the real case of this is massively larger. I'm sure there's a way to do this, I'm just not sure what that is right now.

Upvotes: 1

Views: 84

Answers (1)

JohnHC
JohnHC

Reputation: 11195

You are trying to count distinct rows, but not using a count(distinct ...)

SELECT 
    COUNT(distinct c.id) as "Total Customers",
    COUNT(distinct p.id) as "Total Sales",
    COUNT(distinct c.id) * 1.00 / COUNT(distinct p.id) as "Sales per customer"
FROM test_customers c
    LEFT OUTER JOIN test_purchases p ON c.id = p.cid

Note, performance is not great

Upvotes: 1

Related Questions