Jump to content

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


Praveen RY

Recommended Posts

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

post-202251-0-58021100-1474973867_thumb.jpg

Edited by Praveen RY
Link to comment
Share on other sites

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...