MySQL Forums
Forum List  »  Newbie

Newbie: Sub-query help
Posted by: Charles Solley
Date: June 24, 2015 01:19PM

I have created a query that pulls a 365 day online order history for customers that placed an online order in May. The output is grouped by state and customer ID and counts # of premium items ordered and # of orders. I need to refine the query to only output those customers that had 2 or more orders within 30 days, including order in May. I've been trying to figure out how incorporate these statements into the current query:
MAX("COMP_USERV"."ORDER_MART_V"."ORDER_DT")

and then use the MAX (ORDER_DT) to subtract ORDER_DT from 365 history.

Here is the current query I was hoping to refine:

SELECT ALL
("COMP_USERV"."ORDER_MART_V"."STATE_CD") "STATE_CD" ,
("COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID") "CUSTOMER_ID" ,
SUM("COMP_USERV"."ORDER_MART_V"."PREMIUM_ITEM_ORDERD") "PREMIUM_ITEM_ORDERD" ,
COUNT("COMP_USERV"."ORDER_MART_V"."ORDER_ID") "ORDERS"

FROM "COMP_USERV"."ORDER_MART_V"
WHERE (("COMP_USERV"."ORDER_MART_V"."ORDER_DT" >= CAST(CURRENT_DATE - INTERVAL '365' DAY AS FORMAT 'YYYY-MM-DD'))
AND ("COMP_USERV"."ORDER_MART_V"."ONLINE_INDICATOR" = '1')
AND ("COMP_USERV"."ORDER_MART_V"."TELMKT_IND" = '0'))
AND ("COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID" IN
(SELECT ALL
("COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID")

FROM "COMP_USERV"."ORDER_MART_V"
WHERE (("COMP_USERV"."ORDER_MART_V"."ORDER_DT" BETWEEN '2015-05-01' AND '2015-05-31')
AND ("COMP_USERV"."ORDER_MART_V"."ONLINE_INDICATOR" = '1')
AND ("COMP_USERV"."ORDER_MART_V"."TELMKT_IND" = '0'))
GROUP BY "COMP_USERV"."ORDER_MART_V"."STATE_CD","COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID" ))

GROUP BY "COMP_USERV"."ORDER_MART_V"."STATE_CD","COMP_USERV"."ORDER_MART_V"."CUSTOMER_ID"

Options: ReplyQuote


Subject
Written By
Posted
Newbie: Sub-query help
June 24, 2015 01:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.