user3099298 user3099298 - 5 months ago 7
SQL Question

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

Answer

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(*).

Comments