CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
Tora Tora Tora
Tora Tora Tora

Reputation: 993

SQL: Sort Results of a Query Based on the Order of values from another Query

Suppose I have a table with 3 columns:

And I wanted to display the following:

The additional requirement being that the COMPANY with the Maximum Sales (across all departments) shows first, and the rest of the results ordered accordingly.

How would I go about writing my query?

I've tried the following, and it doesn't work:

SELECT t1.company,
   cs1.deparment, 
   SUM(cs1.sales) 
FROM   company_sales cs1, 
   (SELECT cs2.company, 
                   SUM(cs2.sales) 
            FROM   company_sales cs2
            WHERE  cs2.company IS NOT NULL 
            GROUP  BY cs2.company 
            ORDER  BY 2 DESC) t1
WHERE  cs1.company = t1.company 
GROUP  BY t1.company,  
      cs1.deparment;

Upvotes: 3

Views: 733

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can do this using window functions:

select company, department, sum(sales)
from t
group by company, department
order by sum(sum(sales)) over (partition by company) desc, company;

You can also include the expression in the select clause to see the sum of sales for the entire company.

Upvotes: 3

Anton
Anton

Reputation: 420

Try:

 SELECT company,department,sum(sales) 
 FROM table GROUP BY company,department 
 ORDER BY Max(sales)

Upvotes: 0

Related Questions