CODEWITHSUNDEEP
sqlsql-server
Gayan
Gayan

Reputation: 2935

How to Order SQL Query using two columns?

accounts table
----------------

id  name
10  ABC Company     
11  XYZ Company 
12  LMN Company 
13  EFG Company 
14  JKL Company 
..  ...........
..  ...........

accounts_opportunities table
-----------------------
id  opportunity_id    account_id    deleted
1   1         11        0
2   2         11        0
3   3         12        0
4   4         12        0
5   5         13        0
6   6         14        0
.   .         ..        .
.   .         ..        .


opportunities table
-----------------
id  name    amount
1   Opp 1   100
2   Opp 2   50
3   Opp 3   500
4   Opp 4   600
5   Opp 5   200
6   Opp 6   1000

I am trying to select top 20 accounts from above tables. And I written following query for that,

SELECT TOP 20 COUNT(*) as number_of_opportunities, 
    (
        SELECT accounts.name  
        FROM accounts 
        WHERE accounts.id=accounts_opportunities.account_id
    ) as account_name
FROM accounts_opportunities 
JOIN opportunities ON opportunities.id = accounts_opportunities.opportunity_id 
WHERE accounts_opportunities.deleted != '1'   
GROUP BY accounts_opportunities.account_id
ORDER BY number_of_opportunities DESC

And above query gives following output;

Account name    Number of Opportunities
------------    -----------------------
ABC Company     3 

XYZ Company     2 
LMN Company     2

EFG Company     1
JKL Company     1

XYZ and LMN have same numbers opportunities, but if I calculate total amount of XYZ opportunities are less than LMN total amount. Also JKL has higher amount than EFG.

My question is how to order this query from number_of_opportunities and then total opportunity amount. can someone please give me a guide..

Expected Output

Account name    Number of Opportunities
------------    -----------------------
ABC Company     3 
LMN Company     2
EFG Company     1
JKL Company     1
XYZ Company     2

Upvotes: 2

Views: 59

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT TOP 20 a.name, COUNT(*) AS [Number of Opportunities]
FROM accounts AS a
INNER JOIN accounts_opportunities AS ao ON a.id = ao.account_id
INNER JOIN opportunities AS o ON o.id = ao.opportunity_id 
WHERE ao.deleted != 1
GROUP BY a.id, a.name   
ORDER BY COUNT(*) DESC, SUM(amount) DESC

So, you can add SUM(amount) in the ORDER BY clause with DESC. This will place accounts with higher amount sums on top of other accounts, in case of a tie in COUNT(*).

Upvotes: 1

Related Questions