Praveen RY

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');

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.

September 27, 2016

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_created
FROM events e INNER JOIN events et ON e.user_id = et.user_id
WHERE 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

Sign In

Praveen RY

Posts

Joined

Last visited

Content Type

Profiles

Forums

Events

Posts posted by Praveen RY

Need help building mysql query to count number of data under same row/table

Need help building mysql query to count number of data under same row/table

Need help building mysql query to count number of data under same row/table

Browse

Activity