This discussion is archived
1 Reply Latest reply: Feb 14, 2013 1:58 AM by AlbertoFaenza RSS

query help

Vi Explorer
Currently Being Moderated
Hi ,

I need a query help. I have data in table called order_points like below. I need to display user information for each 3000 in total and once for every 500 over 3000.


1 A 01/02/2013 2000
1 A 01/03/2013 1000
1 A 01/04/2013 750
1 A 01/05/2013 500
2 B 01/01/2013 3620

Example: user A has total points 4150 , user would be listed 3 times like below. user B has total points 3620 so would be listed 2 times like below.

output:

1 A
1 A
1 A
2 B
2 B
create table order_points(user_id NUMBER(10),user_name VARCHAR2(20),order_date DATE, points NUMBER(18))
/
INSERT INTO order_points VALUES (1,'A','02-JAN-2013',2000)
/
INSERT INTO order_points VALUES (1,'A','03-JAN-2013',1000)
/
INSERT INTO order_points VALUES (1,'A','04-JAN-2013',750)
/
INSERT INTO order_points VALUES (1,'A','05-JAN-2013',500)
/
INSERT INTO order_points VALUES (2,'B','01-JAN-2013',3620)
/
  • 1. Re: query help
    AlbertoFaenza Expert
    Currently Being Moderated
    Here is a solution:
    WITH mydata AS 
    (  
       SELECT user_id, user_name
            , CEIL ( (SUM (points) - 3000) / 500) n
         FROM order_points
        GROUP BY user_id, user_name
    )
    , max_times AS
    ( 
       SELECT LEVEL lvl
         FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (n) FROM mydata)
    )
    SELECT a.user_id, a.user_name
      FROM mydata a, max_times b
     WHERE b.lvl <= a.n
     ORDER BY a.user_id, a.user_name;
    
       USER_ID USER_NAME           
    ---------- --------------------
             1 A                   
             1 A                   
             1 A                   
             2 B                   
             2 B                 
    Regards.
    Al

    Edited by: Alberto Faenza on Feb 14, 2013 10:57 AM
    Modified with an additional WITH for readability

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points