CODEWITHSUNDEEP
sqlsql-server
David Smith
David Smith

Reputation: 147

Problems with group by and order by

Hi I am a newbie to the world of sql but struggling to get some of the basics to work.

I have a set of data 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 end up looking 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 greatest TOTAL amount

Group by does not work for me as it groups all projects into one, so I can't see the 3 work order lines for "Project 111"

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

Order by does not work as I can't get it sort it out on the basis of the greatest 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 if I could create another column "Project Amount" that calculates the projects total based on values in "Project" column and I can then easily sort it by Project Amount instead 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 am struggling how to get column "Project Amount" to calculate all the projects total value and present them on any rows that appear with the same project number.

Any advise?

Upvotes: 4

Views: 106

Answers (3)

Ahmed
Ahmed

Reputation: 450

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

Upvotes: 0

Andomar
Andomar

Reputation: 238048

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

Example at SQL Fiddle.

To select only the top two projects with the highest amounts, you could 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 at SQL Fiddle.

Upvotes: 4

peterm
peterm

Reputation: 92785

A version with plain SQL subquery

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

SQLFiddle

Upvotes: 1

Related Questions