sql rank, partition suggest.

srikanthrad
srikanthrad used Ask the Experts™
on
       select sum(quantity), contract, price from (
        SELECT contract, price, quantity,
       RANK() OVER (PARTITION BY contract ORDER BY price) AS rank_no
  FROM (
SELECT 100 AS price, 'contract1' AS contract, '1' as quantity FROM dual UNION ALL
SELECT 200 AS price, 'contract2' AS contract, '4' as quantity FROM dual UNION ALL
SELECT 200 AS price, 'contract2' AS contract, '3' as quantity FROM dual UNION ALL
SELECT 300 AS price, 'contract3' AS contract, '2' as quantity FROM dual)
)a group by (rank_no, contract, quantity, price)    


Currently, I am getting

SUM(QUANTITY)      CONTRACT      PRICE

2      contract3      300
4      contract2      200
3      contract2      200
1      contract1      100


What I need is

SUM(QUANTITY)      CONTRACT      PRICE

2      contract3      300
7      contract2      200
1      contract1      100
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
select sum(quantity), contract, price
from (
        SELECT contract, price, quantity,
          RANK() OVER (PARTITION BY contract ORDER BY price) AS rank_no
        FROM (
          SELECT 100 AS price, 'contract1' AS contract, '1' as quantity FROM dual UNION ALL
          SELECT 200 AS price, 'contract2' AS contract, '4' as quantity FROM dual UNION ALL
          SELECT 200 AS price, 'contract2' AS contract, '3' as quantity FROM dual UNION ALL
          SELECT 300 AS price, 'contract3' AS contract, '2' as quantity FROM dual
        )
)a
group by contract, price    

Author

Commented:
It is working fine. Thanks a bunch. Is there a better way of writing query or is that it?
Reza RadConsultant, Trainer

Commented:
I don't understand why you want to use rank in this query!

select sum(quantity), contract, price from (
        SELECT 100 AS price, 'contract1' AS contract, '1' as quantity FROM dual UNION ALL
SELECT 200 AS price, 'contract2' AS contract, '4' as quantity FROM dual UNION ALL
SELECT 200 AS price, 'contract2' AS contract, '3' as quantity FROM dual UNION ALL
SELECT 300 AS price, 'contract3' AS contract, '2' as quantity FROM dual)
)a group by (contract, price)

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
I am actually not sure why you have the rank() in there since it's not needed AFAICT. So technically the code below would solve the question, too:

SELECT sum(quantity), contract, price
FROM (
  SELECT 100 AS price, 'contract1' AS contract, '1' as quantity FROM dual UNION ALL
  SELECT 200 AS price, 'contract2' AS contract, '4' as quantity FROM dual UNION ALL
  SELECT 200 AS price, 'contract2' AS contract, '3' as quantity FROM dual UNION ALL
  SELECT 300 AS price, 'contract3' AS contract, '2' as quantity FROM dual
) group by contract, price

Author

Commented:
And also, I wanted to add some more cols to select...

 select sum(quantity), contract, price, somecol
from (

          SELECT 100 AS price, 'contract1' AS contract, '1' as quantity, 'idcol' as somecol FROM dual UNION ALL
          SELECT 200 AS price, 'contract2' AS contract, '4' as quantity, 'idcol2' as somecol FROM dual UNION ALL
          SELECT 200 AS price, 'contract2' AS contract, '3' as quantity, 'idcol3' as somecol FROM dual UNION ALL
          SELECT 300 AS price, 'contract3' AS contract, '2' as quantity, 'idcol4' as somecol FROM dual
)a
group by contract, price, somecol    

however is not working fine. Please suggest for this as well.

Commented:
What is it you're trying to do that is "not working fine"?
Reza RadConsultant, Trainer
Commented:
>>however is not working finehowever is not working fine
this seems to be correct?
why  you say don't working fine? do you mean another data than 4 rows returned from this query?

select * from (select sum(quantity) over (partition by  contract order by  contract ) con_sum, contract, price,somecol,
         row_number() over (partition by  contract order by  contract ) rno
from (      SELECT 100 AS price, 'contract1' AS contract, '1' as quantity, 'idcol' as somecol FROM dual UNION ALL
          SELECT 200 AS price, 'contract2' AS contract, '4' as quantity, 'idcol2' as somecol FROM dual UNION ALL
          SELECT 200 AS price, 'contract2' AS contract, '3' as quantity, 'idcol3' as somecol FROM dual UNION ALL
          SELECT 300 AS price, 'contract3' AS contract, '2' as quantity, 'idcol4' as somecol FROM dual
)a)z
where z.rno=1

try this will help u
Shaju KumbalathDeputy General Manager - IT

Commented:
in the above post in the output u can not find 'idcol3' as it is not in the current group.
So pls give provide some examples what are u actually looking for .
 
awking00Information Technology Specialist

Commented:
To answer the question for your first comment, "Is there a better way of writing query ...?" the attached uses only the one subquery and might work better although I haven't tested its performance. As for the additional columns issue, using the example, what would the expected output be? Would you want a row for both somecol values for contract2 (i.e. idcol2 and idcol3) or still just one row for contract 2 with a particular somecol value (e.g. max or min)?
query.txt
awking00Information Technology Specialist

Commented:
Sorry, I attached the wrong query. Try again.
query.txt
awking00Information Technology Specialist

Commented:
Your accepted solution and the first three assisted solutions don't deal with the addition of the somecol and the fourth assisted solution doesn't deal with the case where the contract equals contract2 and the somecol equals idcol3, as shajukq pointed out.

Author

Commented:
@awking:

SELECT distinct sum(quantity) OVER (PARTITION BY contract, price ORDER BY price) qty
      ,contract, price
FROM
(SELECT 100 AS price, 'contract1' AS contract, '1' as quantity FROM dual UNION ALL
 SELECT 200 AS price, 'contract2' AS contract, '4' as quantity FROM dual UNION ALL
 SELECT 200 AS price, 'contract2' AS contract, '3' as quantity FROM dual UNION ALL
 SELECT 300 AS price, 'contract3' AS contract, '2' as quantity FROM dual)
order by price desc;

SELECT sum(quantity), contract, price
FROM (
  SELECT 100 AS price, 'contract1' AS contract, '1' as quantity FROM dual UNION ALL
  SELECT 200 AS price, 'contract2' AS contract, '4' as quantity FROM dual UNION ALL
  SELECT 200 AS price, 'contract2' AS contract, '3' as quantity FROM dual UNION ALL
  SELECT 300 AS price, 'contract3' AS contract, '2' as quantity FROM dual
) group by contract, price

Both queries are giving me the same result, this is what I want.

2      contract3      300
7      contract2      200
1      contract1      100

I wanted idcol3 or idcol2.  So, I accepted the solution.

But, what if I wanted both the columns. May I ask Is there a wtay to get both columns with sum(qty) being shown for both idcol3 and idcol2?


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial