Problems with the group in order and order

Question

Hi, I am new to the sql world, but am struggling to get some of the reasons to work.

I have a dataset that looks like this:

Table name: Sample PROJECT WORK ORDER AMOUNT ----------------------------------------- 111 a 100 222 b 200 111 c 300 444 d 400 111 e 500 666 f 600 

I want it to look like this:

 Table name: Sample PROJECT WORK ORDER AMOUNT PROJECT AMOUNT -------------------------------------------------------- 111 e 500 900 111 c 300 900 111 a 100 900 666 f 600 600 444 d 400 600 222 b 200 200 

Sorted by project with the highest total TOTAL

The group does not work for me, because it combines all the projects into one, so I do not see three lines of the execution order for "Project 111"

 PROJECT WORK ORDER AMOUNT ----------------------------------------- 111 a 900 222 b 200 444 d 400 666 f 600 

The order does not work, because I cannot sort it based on the highest project value

 Table name: Sample PROJECT WORK ORDER AMOUNT ----------------------------------------- 666 f 600 111 e 500 444 d 400 111 c 300 222 b 200 111 a 100 

My alternative idea was that I could create another “Project Amount” column, which calculates the total number of projects based on the values ​​in the “Project” column, and then I can easily sort it by the project amount to achieve the desired format

 Table name: Sample PROJECT WORK ORDER AMOUNT PROJECT AMOUNT -------------------------------------------------------- 111 e 500 900 111 c 300 900 111 a 100 900 666 f 600 600 444 d 400 600 222 b 200 200 

But I’m afraid how to get the “Project Amount” column to calculate the total number of projects and present them in any lines that appear with the same project number.

Any tips?

Answer 1

 select * , sum(amount) over (partition by project) as ProjAmount , row_number() over from YourTable order by ProjAmount desc 

Example in SQL Fiddle.

---

To select only the two best projects with the highest amount, you can use dense_rank :

 select * from ( select * , dense_rank() over (order by ProjAmount desc) as dr from ( select * , sum(amount) over (partition by project) as ProjAmount from YourTable ) WithProjAmount ) WithDenseRank where dr < 3 order by ProjAmount desc 

Example in SQL Fiddle.

Answer 2

Version with a simple SQL subquery

 SELECT s.*, (SELECT SUM(Amount) FROM Sample WHERE Project = s.Project) ProjectAmount FROM Sample s ORDER BY ProjectAmount DESC 

SQLFiddle

Answer 3

 SELECT a.project , a.work , a.amount , b.proj_amount FROM project A JOIN ( SELECT SUM(amount) proj_amount , project FROM project WHERE project = project GROUP BY project ) b ON a.project = b.project ORDER BY proj_amount DESC , amount DESC