CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
shaik moeed
shaik moeed

Reputation: 5795

Getting duplicate records after using WITH AS

I have three select queries which gives count of records of last day of months(current, current-1, current-2) group by hour.

I'm trying to make it single query where all three outputs comes side by side.

I have used with as to do this job.

Issue is getting duplicate records(nearly 2k records) where expected max row is 24(cuz 24 hours per day).

Query: 

with curr as 
(
  Select
    last_day(add_months(trunc(sysdate, 'mm'), 0)),
    TO_CHAR(created_date, 'HH24') as "Time",
    count(1) as "Count"
  from
    table1
  where cretd_date >= trunc(last_day(add_months(trunc(sysdate, 'mm'), 0)))
  group by
    TO_CHAR(created_date, 'HH24'),
    last_day(add_months(trunc(sysdate, 'mm'), 0))
  order by
    TO_CHAR(cretd_date, 'HH24')
),
curr_1 as 
(
  Select
    last_day(add_months(trunc(sysdate, 'mm'), -1)),
    TO_CHAR(created_date, 'HH24') as "Time",
    count(1) as "Count"
  from
    table1
  where cretd_date >= trunc(last_day(add_months(trunc(sysdate, 'mm'), -1)))
  group by
    TO_CHAR(created_date, 'HH24'),
    last_day(add_months(trunc(sysdate, 'mm'), -1))
  order by
    TO_CHAR(cretd_date, 'HH24')
),
curr_2 as
(
  Select
    last_day(add_months(trunc(sysdate, 'mm'), -2)),
    TO_CHAR(created_date, 'HH24') as "Time",
    count(1) as "Count"
  from
    table1
  where cretd_date >= trunc(last_day(add_months(trunc(sysdate, 'mm'), -2)))
  group by
    TO_CHAR(created_date, 'HH24'),
    last_day(add_months(trunc(sysdate, 'mm'), -2))
  order by
    TO_CHAR(cretd_date, 'HH24')
)
select * from curr, curr_1,curr_2;

Acutal output:

2k repeated rows

Expected output:

max 24 rows

last_date  | time   | count   | last_date  | time_1 | count_1 | last_date | time_2| count_2 |
31-july-19 | 00     | 2       | 31-June-19 | 00     | 1       | 31-May-19 | 00    | 3       |
...
31-july-19 | 23     | 34      | 31-June-19 | 23     | 23      | 31-May-19 | 23    | 32      |

* If there is any other optimal approach to achieve same, please share.

Upvotes: 0

Views: 68

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

I presume you want to pivot the monthly results, you may use conditional aggregation.

SELECT TO_CHAR(created_date, 'HH24') AS "Time"
    ,last_day(sysdate) AS m1
    ,last_day(add_months(sysdate, - 1)) m2
    ,last_day(add_months(sysdate, - 2)) m3
    ,count(CASE 
            WHEN created_date >= trunc(last_day(sysdate))
                THEN 1
            END) m1_count
    ,count(CASE 
            WHEN created_date >= trunc(last_day(add_months(sysdate, - 1)))
                THEN 1
            END) m2_count
    ,count(CASE 
            WHEN created_date >= trunc(last_day(add_months(sysdate, - 2)))
                THEN 1
            END) m3_count
FROM table1
GROUP BY TO_CHAR(created_date, 'HH24')
ORDER BY "Time";

Note: As you've not provided data, this is untested query.Please try and let me know.

Upvotes: 2

Popeye
Popeye

Reputation: 35930

You are missing joins between CURR, CURR_1, CURR_2.

Join should be based on column "Time".

So Your SELECT query should be something like this:

SELECT
    *
FROM
    CURR JOIN
    CURR_1 USING("Time") JOIN
    CURR_2 USING("Time");

Cheers!!

Upvotes: 0

Related Questions