Praveen RY Posted September 27, 2016 Share Posted September 27, 2016 (edited) Table name "events" user_id event_type Created 123 Flash Client log 25-Sep-16 127 Client Cyber log 25-Sep-16 123 Flash Client log 25-Sep-16 126 Flash Client log 25-Sep-16 185 Monitor log 25-Sep-16 146 Delete log 25-Sep-16 173 Flash Client log 26-Sep-16 183 Client Cyber log 26-Sep-16 193 Flash Client log 26-Sep-16 204 Monitor log 27-Sep-16 214 Delete log 27-Sep-16 225 Flash Client log 27-Sep-16 Expected result Date Flash Clien 25-Sep-16 2 1 26-Sep-16 2 1 27-Sep-16 1 0 I was using below query but this wont show the required result. SELECT COUNT (DISTINCT e.user_id) AS Flash,COUNT (DISTINCT et.user_id) AS Client,TO_CHAR (e.created) AS Date_createdFROM events e INNER JOIN events et ON e.user_id = et.user_idWHERE e.created BETWEEN '25-SEP-15' AND '27-SEP-15'AND e.event_type = 'Flash Client log'AND et.event_type = 'Client Cyber log'GROUP BY TO_CHAR (e.created); ** I have uploaded image of the table as the data provided above is not aligned and I did not get insert table on this forum post Edited September 27, 2016 by Praveen RY Link to comment Share on other sites More sharing options...
Praveen RY Posted September 28, 2016 Author Share Posted September 28, 2016 To make this more simple I have two query which gives me results. I want to combine these tow query with keeping date as common from both the query. 1st Query : I get the distinct user_id count of "Flash client Log" date wise SELECT to_char(created),count(distinct user_id) as Flash_log from events where event_type = 'Flash Client Log' and created between '25-SEP-16' AND '27-SEP-16' group by to_char(created) order by to_char(created) asc 2nd Query : I get the distinct user_id of "Client Cyber Log" SELECT to_char(created), count(distinct user_id) as Client_log from events where event_type = 'Client Cyber Log' and created between '25-SEP-16' AND '27-SEP-16' group by to_char(created) order by to_char(created) asc I wanted to combine the results of 'Flash Client Log' & "Client Cyber Log" side by side arranged date wise. Any help on this is much appreciated. Link to comment Share on other sites More sharing options...
Praveen RY Posted September 29, 2016 Author Share Posted September 29, 2016 I figured this our. pasting this final query of mine just in case if it will help any one. select to_char(cast(created as date),'YYYY-MM-DD') as Report_date, count(distinct(case when event_type in ('Flash C Log') then user_id else 0 end)) as Flash_Log, count(distinct(case when (event_type = 'Client C Log') then user_id else 0 end)) as Client_Log from events e where created between '25-SEP-16' and '27-SEP-16' group by to_char(cast(created as date),'YYYY-MM-DD') order by to_char(cast(created as date),'YYYY-MM-DD'); Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now