To get the data for last 3 day trend

Hi

You can use Group by clause so as not to use UNION ALL

your select should be something like this

SELECT TRUNC(Created_Date) 3Day_Trend, COUNT(1)

FROM ORDER

WHERE TRUNC(Created_Date) BETWEEN TRUNC(SYSDATE)-2 AND TRUNC(SYSDATE)

GROUP BY TRUNC(Created_Date)

Regards

Salim

Guna,

You would get better response, if you close this and raise the question in SQL & PL/SQL

One option (not the best option)

select (sysdate-rownum+1) "3Day_Trend" , val total_count   
 from  (select sum (case  
                    when Created_Date <= TRUNC(SYSDATE) then 1 else 0 end) column1
              ,sum (case  
                    when Created_Date <= TRUNC(SYSDATE-1) then 1 else 0 end) column2
              ,sum (case  
                    when Created_Date <= TRUNC(SYSDATE-2) then 1 else 0 end) column3
from Order)
unpivot (val for col in(column1, column2, column3));

Cheers

AJ

Have a look at the analytical functions:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions039.htm#SQLRF00624

WITH src AS (
  SELECT
    trunc(Created_Date) AS dy
  , COUNT(*) AS cnt
  FROM order
  WHERE Created_Date > trunc(SYSDATE) - 3
  GROUP BY trunc(Created_Date)
  )
SELECT
  dy AS "2Day_Trend"
, SUM(cnt) over (ORDER BY dy) AS "Total_count"
FROM src;

You might want to refine this using moving windows, etc.

Then please mark the thread ANSWERED and note that this forum is ONLY for Sql Developer questions.

In the future post SQL questions in the SQL and PL/SQL forum.

SQL & PL/SQL