Hi, i have managed to write the following code to give me an output but for some reason I am not able to display the min(time) value as I get 0094 error which identifies that field does not exist.
the following is the code:
SELECT ID, username, order_num, count(*) AS Count, search_time, Owner,
count(*) over (partition by Owner) AS Owner_Count,
sum(decode(count(order_num),1,1,0)) over (partition by Owner) only_1,
round(sum(decode(count(order_num),1,1,0)) over (partition by Owner)/ (count(*) over (partition by Owner)) * 100, 1) AS Answer
FROM
(
SELECT distinct decode(Tbl1.Order_Num, 'NA', Tbl1.telephone_number, Tbl1.Order_Num) Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username, min(Tbl2.search_time), Tbl1.Product, Tbl1.Owner
FROM Tbl2 INNER JOIN Tbl1 ON Tbl2.telephoneno = Tbl1.telephone_number
where Tbl1.data_date between '10-Dec-12 00:00:00' and '16-Dec-12 23:59:59'
group by
Tbl1.Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username
, trunc(Tbl2.search_time), search_time -- to group just by day
, Tbl1.Product, Tbl1.Owner
UNION ALL
SELECT distinct decode(Tbl1.Order_Num, 'NA', Tbl1.telephone_number, Tbl1.Order_Num) Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username, min(Tbl2.search_time), Tbl1.Product, Tbl1.Owner
FROM Tbl2 INNER JOIN Tbl1 ON Tbl2.faultrefno = Tbl1.Order_Num
where Tbl1.data_date between '10-Dec-12 00:00:00' and '16-Dec-12 23:59:59'
group by
Tbl1.Order_Num, Tbl1.Telephone_Number, Tbl2.ID, Tbl2.username
, trunc(Tbl2.search_time) -- to group just by day
, Tbl1.Product, Tbl1.Owner
)
group by ID, username, order_num, search_time, Owner;
When the above code is run I get the following error:
ORA-00904: "SEARCH_TIME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 172 Column: 45
Can any1 help me understand how I can display the min Search_time in my final table?
Thanks in advance.