Aggregate data in oracle day wise or week wise or month wise

Question

0.00/5 (No votes)

See more:

Hi All,

I could not explain my problem in my last question so I am posting a fresh question.

I have a table:

ename | edate
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 02-10-17
emp2 | 01-10-17
emp3 | 01-10-17
emp3 | 01-10-17
emp3 | 02-10-17
emp3 | 02-10-17

Expected output after pivoting:
ename | 01-10-17 | 02-10-17
emp1 | 3 | 1
emp2 | 1 | 0
emp3 | 2 | 2

and there is condition that count for each day should be greater than 1.

So final out put is
ename | 01-10-17 | 02-10-17
emp3 | 2 | 2

as for only emp 3 count for all days is more than 1.

I have used pivot in oracle 11g. But the actually this query will run on 10g and I found out that pivot is not supported in 10g.

What I have tried:

In oracle 11g,

SELECT * FROM
(
SELECT ename, edate
FROM tblName
)
PIVOT
(
COUNT(edate)
FOR edate in('01-oct-2017', '02-oct-2017')
)
ORDER BY ename;

But I cannot find way to add the "> 1" condition and also now I have typed the dates manually but in real situation they can be any multiple dates.

Posted 12-Oct-17 1:54am

planetz

Updated 12-Oct-17 3:46am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-10-12T03:46:00

[EDIT]
If you would like to get only these data where count is bigger than 1, you can use HAVING clause[^] to limit result set as a source of pivot.

SQL

SELECT ename, edate, COUNT(*) AS CountOfDates
FROM tblName
GROUP BY ename, edate
HAVING COUNT(*)>1

As to your second question: there's a way to create pivot table without typing dates: Dynamic pivot in oracle sql - Stack Overflow[^]
So, i would try (but i'm not having installed Oracle server) to improve referenced sql statements:

clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || edate || ''' AS ' || edate,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT edate FROM tblName);

--this should put a list of dates in format: <code>'01-Oct-2017' as 01-Oct-2017</code> and so on...  into a variable

SELECT *
FROM (
    SELECT ename, edate, COUNT(*) AS CountOfDates
    FROM tblName
    GROUP BY ename, edate
    HAVING COUNT(*)>1
)
PIVOT (Sum(CountOfDates) AS val FOR edate IN (&str_in_statement));

Note: There are limitations though: You can only concatenate a string up to 4000 bytes.

For further details, please see:
Oracle SQL*PLUS new_value parameter[^]
Command Reference, 14 of 52[^]