Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help (merged 2 cross-posts)
SQL query help (merged 2 cross-posts) [message #259522] Wed, 15 August 2007 16:18 Go to next message
kalyan_218
Messages: 4
Registered: August 2007
Junior Member
I have two tables User_dim and User_daily_activity_fact table

User_dim has following fields

USER_KEY ---- OPERATOR_PARTITION_KEY

56103 --- 0
56104 ---- 0
56105 --- 0
10201 --- 0



User_daily_activity_fact


OPERATOR_PARTITION_KEY ---- PRODUCT_KEY --- USER_KEY -- DATE_KEY--- COMMAND_KEY

0 ---- 1 -- 56103 -- 20070505 -- 2


0 ---- 1 -- 56104 -- 20070505 --- 3

0 --- 1 -- 56104 -- 20070505 -- 4











select f.operator_partition_key, f.date_key start_date_key,f.date_key end_date_key,
COUNT(DISTINCT f.user_key) active_user_cnt,
COUNT(DECODE(f.command_key, 3, 1, 4, 1)) message_countFROM ngmadm.user_daily_activity_fact f,
user_dim dWHERE d.user_key = f.user_key
GROUP BY f.operator_partition_key, f.date_key, product_key;


This query works now i need to add Inactive_user_cnt in the above query which is

Total number of users in user_dim table minus total number of active users in user_daily_activity_table

I tried to use this query below but it gives some errorr.



select f.operator_partition_key, f.date_key start_date_key,f.date_key end_date_key,
COUNT(DISTINCT f.user_key) active_user_cnt,
count(select user_key from user_dim minus select distinct(user_key) from user_daily_activity_fact) inactive_usrs,
COUNT(DECODE(f.command_key, 3, 1, 4, 1)) message_count
FROM ngmadm.user_daily_activity_fact f,
user_dim d
WHERE d.user_key = f.user_key GROUP BY f.operator_partition_key,
f.date_key, product_key;




Thanks
_k
Re: Sql query help [message #259523 is a reply to message #259522] Wed, 15 August 2007 16:19 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Please read & FOLLOWing the posting guidelines in the STICKY post at top of forum.
Your post is borderline unreadable as originally posted.
Re: Sql query help [message #259525 is a reply to message #259522] Wed, 15 August 2007 18:30 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
This query works now i need to add Inactive_user_cnt in the above query which is

Total number of users in user_dim table minus total number of active users in user_daily_activity_table

You shall use outer join, so add the outer join operator (+) to the join condition (see below).
Total number of user can be counted as COUNT(DISTINCT d.user_key).
SELECT
  f.operator_partition_key,
  f.date_key start_date_key,
  f.date_key end_date_key,
  COUNT(DISTINCT f.user_key) active_user_cnt,
  COUNT(DECODE(f.command_key, 3, 1, 4, 1)) message_count
FROM
  ngmadm.user_daily_activity_fact f,
  user_dim d
WHERE
  d.user_key = f.user_key(+)
GROUP BY
  f.operator_partition_key,
  f.date_key,
  product_key;

See How to format your post sticky to use the same code formatting in your next posts.
Do you immediately see, that you miscopied the last column of the GROUP BY clause. I wonder how long it would take to you or anybody else to find it in the original unformatted post. If anybody would bother to try...
SQl Help [message #259870 is a reply to message #259522] Thu, 16 August 2007 13:26 Go to previous messageGo to next message
kalyan_218
Messages: 4
Registered: August 2007
Junior Member
I have two tables User_dim and User_daily_activity_fact table

User_dim has following fields

USER_KEY ---- OPERATOR_PARTITION_KEY

56103 --- 0
56104 ---- 0
56105 --- 0
10201 --- 0



User_daily_activity_fact


OPERATOR_PARTITION_KEY ---- PRODUCT_KEY --- USER_KEY -- DATE_KEY--- COMMAND_KEY

0 ---- 1 -- 56103 -- 20070505 -- 2


0 ---- 1 -- 56104 -- 20070505 --- 3

0 --- 1 -- 56104 -- 20070505 -- 4

0 -------- 2 ------- 56105 ---20070605---3










select f.operator_partition_key, f.date_key start_date_key,f.date_key end_date_key,
COUNT(DISTINCT f.user_key) active_user_cnt,
COUNT(DECODE(f.command_key, 3, 1, 4, 1)) message_count
FROM user_daily_activity_fact f,
user_dim dWHERE d.user_key = f.user_key
GROUP BY f.operator_partition_key, f.date_key, product_key;


This query works now i need to add Inactive_user_cnt in the above query which is count of

total number of users in user_dim table minus total number of active users in user_daily_activity_table

I tried to use this query below but it gives some errorr.



select f.operator_partition_key, f.date_key start_date_key,f.date_key end_date_key,
COUNT(DISTINCT f.user_key) active_user_cnt,
count(select user_key from user_dim minus select distinct(user_key) from user_daily_activity_fact) inactive_usrs,
COUNT(DECODE(f.command_key, 3, 1, 4, 1)) message_count
FROM user_daily_activity_fact f,
user_dim d
WHERE d.user_key = f.user_key GROUP BY f.operator_partition_key,
f.date_key, product_key;




output should be in this format based on the example give above

OPERATOR_PARTITION_KEy -- Start_date_key -- end_date_key--active_users---inactive_users---message_cnt

0 -- 20050505 ----- 20050505 --- 3------- 1 ------ 3
Re: SQl Help [message #259871 is a reply to message #259870] Thu, 16 August 2007 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I tried to use this query below but it gives some errorr.
ERROR? What error? I don't see any error.

Read & FOLLOW the guidelines in the STICKY post at top of forum.
Re: SQl Help [message #259876 is a reply to message #259870] Thu, 16 August 2007 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Continue on the previous thread: http://www.orafaq.com/forum/t/87470/96705/
This one is closed.
./fa/448/0/
Regards
Michel
Re: SQl Help [message #259878 is a reply to message #259876] Thu, 16 August 2007 14:02 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unlock after merge.
Michel
Previous Topic: SQL/PLSQL
Next Topic: Changing columns
Goto Forum:
  


Current Time: Sat Dec 03 18:21:38 CST 2016

Total time taken to generate the page: 0.08483 seconds