| Query for the below requirement [message #599785] |
Mon, 28 October 2013 23:49  |
 |
avineshr
Messages: 14
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  |
Lalit Kumar B
Messages: 3174
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 #599792 is a reply to message #599791] |
Tue, 29 October 2013 01:10 |
Lalit Kumar B
Messages: 3174
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?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
