Home » SQL & PL/SQL » SQL & PL/SQL » Query for the below requirement (10.2.0.5 )
Query for the below requirement [message #599785] Mon, 28 October 2013 23:49 Go to next message
avineshr
Messages: 6
Registered: September 2012
Location: malaysia
Junior Member
Dear all,

I need an query for the below data set, please help me.

My data set looks like below.

Requirement is , in below data set Afkar,Promis are the application name , and PWD Reset,New user are the activies performed on application.

I need an result set for application wise activites performed, how to get the output in SQL query



ID	ACTIVITY
-----------------------
1	Afkar
1	PWD Reset
2	Afkar
2	PWD Reset
3	Afkar
3	New User
4	Promise
4	PWD Reset



Output should look like below:


Application    Activity    Count
-------------------------------------
Afkar          PWD Reset     2
Afkar          New User      1
Promise        PWD Reset     1

Re: Query for the below requirement [message #599787 is a reply to message #599785] Tue, 29 October 2013 00:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2069
Registered: May 2013
Location: World Wide on the Web
Senior Member
The data is absurd. There is no connection between the data and the rules you mentioned to get the desired output.

If the data has the combined values of multiple entities, how would you differentiate between the different entities? The data is junk in that sense.

So please explain why the data for "application name" and "activity performed" resides in the same column? What is the rule to differentiate between the two?

One way you could do is first seperate the application name and activity from single data set into two different sets. And then join the tables on their IDs.

SQL> CREATE TABLE t_app_name AS SELECT * FROM(
  2  WITH DATA AS (
  3  SELECT 1	id, 'Afkar' app from dual union ALL
  4  SELECT 1	id, 'PWD Reset' app from dual union ALL
  5  SELECT 2	id, 'Afkar' app from dual union ALL
  6  SELECT 2	id, 'PWD Reset' app from dual union ALL
  7  SELECT 3	id, 'Afkar' app from dual union ALL
  8  SELECT 3	id, 'New User' app from dual union ALL
  9  SELECT 4	id, 'Promise' app from dual union ALL
 10  SELECT 4	id, 'PWD Reset' app from dual)
 11  SELECT ID, APP APP_NAME
 12     FROM DATA
 13   WHERE APP IN ('Afkar', 'Promise'));
 
Table created

SQL> CREATE TABLE t_activity AS SELECT * FROM(
  2  WITH DATA AS (
  3  SELECT 1	id, 'Afkar' app from dual union ALL
  4  SELECT 1	id, 'PWD Reset' app from dual union ALL
  5  SELECT 2	id, 'Afkar' app from dual union ALL
  6  SELECT 2	id, 'PWD Reset' app from dual union ALL
  7  SELECT 3	id, 'Afkar' app from dual union ALL
  8  SELECT 3	id, 'New User' app from dual union ALL
  9  SELECT 4	id, 'Promise' app from dual union ALL
 10  SELECT 4	id, 'PWD Reset' app from dual)
 11  SELECT ID, APP ACTIVITY
 12     FROM DATA
 13    WHERE APP IN ('PWD Reset', 'New User'));
 
Table created

SQL> SELECT APP.ID, APP.APP_NAME, ACT.ACTIVITY, COUNT(act.activity) COUNT
  2    FROM T_APP_NAME APP, T_ACTIVITY ACT
  3   WHERE APP.ID = ACT.ID
  4   GROUP BY app.ID,app.app_name, act.activity
  5   ORDER BY ID;
 
        ID APP_NAME  ACTIVITY       COUNT
---------- --------- --------- ----------
         1 Afkar     PWD Reset          1
         2 Afkar     PWD Reset          1
         3 Afkar     New User           1
         4 Promise   PWD Reset          1



If this does not match your exact requirements, please provide the information as requested in above post.

Regards,
Lalit

[Updated on: Thu, 06 March 2014 13:24] by Moderator

Report message to a moderator

Re: Query for the below requirement [message #599791 is a reply to message #599787] Tue, 29 October 2013 01:02 Go to previous messageGo to next message
avineshr
Messages: 6
Registered: September 2012
Location: malaysia
Junior Member

Thanks for your reply Lalit, I agree that there is no connection between the data and the rules.
since the data is above format we are not able to seggregate.
pls suggest is there any way to get the output in single query.
Regards,
Avinesh
Re: Query for the below requirement [message #599792 is a reply to message #599791] Tue, 29 October 2013 01:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2069
Registered: May 2013
Location: World Wide on the Web
Senior Member
avineshr wrote on Tue, 29 October 2013 11:32

since the data is above format we are not able to seggregate.
pls suggest is there any way to get the output in single query.


I already posted a complete test case on how to seggregate the data and get the output with a single query. Firstly, the sort of data and rules you have mentioned, completely violates normalization.

Look at the two CTAS(create table) scripts, it is to keep application_name and activity in different tables. And then, I have used a single query to join them and get the output. Makes sense?
icon5.gif  Re: Query for the below requirement [message #599794 is a reply to message #599785] Tue, 29 October 2013 01:17 Go to previous message
Michel Cadot
Messages: 58852
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


Why is this not:
Application    Activity    Count
-------------------------------------
PWD Reset      Afkar         2
Afkar          New User      1
PWD Reset      Promise       1

In other words, how can SQL know that an ACTIVITY is an Application or an Activity?
If you can't answer that then SQL can't.

Previous Topic: sql
Next Topic: why it doesnt save in table
Goto Forum:
  


Current Time: Thu Aug 21 05:11:45 CDT 2014

Total time taken to generate the page: 0.10092 seconds