CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3
Brettski
Brettski

Reputation: 1051

PostgreSQL list companies and rank by sales

So I have:

companies (id, name, tenant_id)
invoices (id, company_id, tenant_id, total)

What I want to do is return a result set like:

company    |   Feb Sales  | Feb Rank   | Lifetime Sales | Lifetime Rank
-----------------------------------------------------------------------
ABC Comp   |  1,000       | 1          |  2,000         | 2
XYZ Corp   |    500       | 2          |  5,000         | 1

I can do the sales totals using subselects, but when I do the rank always returns 1. I'm assuming because it only returns 1 row per subselect so will always be the top row?

Here is a piece of the sql:

SELECT
"public".companies."name",
(
    SELECT
        rank() OVER (PARTITION BY i.tenant_id ORDER BY sum(grand_total) DESC) AS POSITION
    FROM
        invoices i 
    where 
        company_id = companies.id
    group by
        i.tenant_id, i.company_id
)
from companies

Upvotes: 0

Views: 179

Answers (1)

PM 77-1
PM 77-1

Reputation: 13344

Below is untested version that can have typos. Please treat it just as description of the approach. For simplicity I assumed that invoices have a month column. 

SELECT
    "public".companies."name", 
     rank() OVER (PARTITION BY sales.companies ORDER BY sales.lifetime) As "Lifetime Rank",
     rank() OVER (PARTITION BY sales.companies ORDER BY sales.month As "One Month"

FROM companies LEFT JOIN 
   (
    SELECT
        SUM(grand_total) As Lifetime,
        SUM(CASE WHEN i.month = <the month of report>, grand_total, 0) As Month
    FROM
        invoices i 
    GROUP BY company_id
   ) sales
ON companies.company_id = sales.company_id

If you run into problems, add the actual code that you used and sample data to your post and I will attempt to create a live demo for you.

Upvotes: 1

Related Questions