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

Question

Suppose I have a table with 3 columns:

YEAR
COMPANY
DEPARTMENT
SALES

And I wanted to display the following:

COMPANY
DEPARTMENT
SUM(SALES)

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;

select company,department,sum(sales) from table group by company,department order by company — ivan
– ivan, Commented Dec 22, 2015 at 23:41
@ivan, there is more to the requirement than just grouping and ordering at one level. Please refer to my question again. — Tora Tora Tora
– Tora Tora Tora, Commented Dec 22, 2015 at 23:47

Gordon Linoff · Accepted Answer · 2015-12-23 02:39:16Z

3

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.

edited Dec 23, 2015 at 2:39

answered Dec 22, 2015 at 23:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tora Tora Tora Over a year ago

Worked like a Charm !! Thanks for the solution.

Paul Maxwell Over a year ago

you require a second condition in that order by clause i.e. , department

Gordon Linoff Over a year ago

@Used_By_Already . . . Actually, it does need a second condition -- company -- to handle ties. I'm not sure what subsequent keys should be.

Paul Maxwell Over a year ago

the rest of the results ordered accordingly. is very unclear but suggests it needs both company (as tie breaker) and department after that. cheers.

Anton · Accepted Answer · 2015-12-22 23:47:59Z

0

Try:

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

answered Dec 22, 2015 at 23:47

Anton

4202 silver badges9 bronze badges

1 Comment

Tora Tora Tora Over a year ago

Anton, this won't work for me, as I'm looking at Sales of a Company across all departments, not just one department.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related