This discussion is archived

# query help

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
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