Afif JABBADO
asked on
sqlite get last time based on message value
SELECT groups.id, groups.name, groups.modified_date, sum(case when events.message = 'Email Sent' then 1 else 0 end) AS 'email_sent', sum(case when events.message = 'Email Opened' then 1 else 0 end) AS 'email_opened', sum(case when events.message = 'Clicked Link' then 1 else 0 end) AS 'clicked_link', sum(case when events.message = 'Submitted Data' then 1 else 0 end) AS 'submitted_data',max(event s.time) as time,events.campaign_id, targets.id, targets.email, targets.first_name, targets.last_name, targets.position from groups left join group_targets gt ON groups.id = gt.group_id left join targets ON targets.id = gt.target_id left JOIN events on events.email = targets.email group by targets.email
i want to get the last time when events.message = 'Submitted Data'
as well the last time when events.message = 'Reported '
gophish.db
I am using sqlite .db i have attached
// GetTargets performs a many-to-many select to get all the Targets for a Group
func GetAllTarget() ([GTarget, error) {
ts := [GTarget{}
err := db.Table("groups").Joins(" left join group_targets gt ON groups.id = gt.group_id").Joins("left join targets ON targets.id = gt.target_id").Joins("left JOIN events on events.email = targets.email").Group("eve nts.campai gn_id,even ts.email") .Select("g roups.id, groups.name, groups.modified_date, sum(case when events.message = 'Email Sent' then 1 else 0 end) AS 'email_sent', sum(case when events.message = 'Email Opened' then 1 else 0 end) AS 'email_opened', sum(case when events.message = 'Clicked Link' then 1 else 0 end) AS 'clicked_link', sum(case when events.message = 'Submitted Data' then 1 else 0 end) AS 'submitted_data',max(event s.time) as time,events.campaign_id, targets.id, targets.email, targets.first_name, targets.last_name, targets.position").Group(" targets.em ail").Orde r("submitt ed_data desc").Limit(10).Scan(&ts) .Error
return ts, err
}
i want to get the last time when events.message = 'Submitted Data'
as well the last time when events.message = 'Reported '
gophish.db
I am using sqlite .db i have attached
// GetTargets performs a many-to-many select to get all the Targets for a Group
func GetAllTarget() ([GTarget, error) {
ts := [GTarget{}
err := db.Table("groups").Joins("
return ts, err
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
select
[groups].[id]
, [groups].[name]
, sum(case when [events].[message] = 'Email Sent' then 1 else 0 end) as 'email_sent'
, sum(case when [events].[message] = 'Email Opened' then 1 else 0 end) as 'email_opened'
, sum(case when [events].[message] = 'Clicked Link' then 1 else 0 end) as 'clicked_link'
, sum(case when [events].[message] = 'Submitted Data' then 1 else 0 end) as 'submitted_data'
, max(case when [events].[message] = 'Submitted Data' then [events].time else null end) as 'LastSubmittedDatatime'
, max(case when [events].[message] = 'Email Reported' then [events].time else null end) as 'LastReportedEmailtime'
, [events].[campaign_id]
, [targets].[id]
, [targets].[email]
, [targets].[first_name]
, [targets].[last_name]
, [targets].[position]
from [groups]
left join [group_targets] as [gt] on [groups].[id] = [gt].[group_id]
left join [targets] on [targets].[id] = [gt].[target_id]
left join [events] on [events].[email] = [targets].[email]
group by [targets].[email],[events].[campaign_id]
[groups].[id]
, [groups].[name]
, sum(case when [events].[message] = 'Email Sent' then 1 else 0 end) as 'email_sent'
, sum(case when [events].[message] = 'Email Opened' then 1 else 0 end) as 'email_opened'
, sum(case when [events].[message] = 'Clicked Link' then 1 else 0 end) as 'clicked_link'
, sum(case when [events].[message] = 'Submitted Data' then 1 else 0 end) as 'submitted_data'
, max(case when [events].[message] = 'Submitted Data' then [events].time else null end) as 'LastSubmittedDatatime'
, max(case when [events].[message] = 'Email Reported' then [events].time else null end) as 'LastReportedEmailtime'
, [events].[campaign_id]
, [targets].[id]
, [targets].[email]
, [targets].[first_name]
, [targets].[last_name]
, [targets].[position]
from [groups]
left join [group_targets] as [gt] on [groups].[id] = [gt].[group_id]
left join [targets] on [targets].[id] = [gt].[target_id]
left join [events] on [events].[email] = [targets].[email]
group by [targets].[email],[events].[campaign_id]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome thank you a lot but small thing can i get the overall sum ? like for the clicked is 4 per email
If you show me in a picture how do you imagine that.
ASKER
Something like that the number are not correct and if submitted Data are duplicated X number it will be computed as one entry for each email/campaign_id than we will get this table with sum of submiteed_data
id | name | email_sent | email_opened | clicked_link | submitted_data | LastSubmittedDatatime | LastReportedEmailtime | campaign_id | id | first_name | last_name | position | |||
1 | cyberx Users | 1 | 0 | 0 | 0 | 7 | 4 | awareness@cyberx-sa.com | |||||||
1 | cyberx Users | 5 | 5 | 4 | 4 | 2020-05-06 12:42:14.6425167+00:00 | 7 | 1 | panda@cyberx-awareness.com | panda | cyberx | Systems Administrator | |||
1 | cyberx Users | 7 | 0 | 0 | 0 | 7 | 2 | stc@cyberx-awareness.com | stc | cyberx | Systems Administrator | ||||
1 | cyberx Users | 7 | 0 | 0 | 0 | 7 | 3 | test@cyberx-awareness.com | test | cyberx | Systems Administrator | ||||
ASKER
the result should be like this
but the issue here in this query that the occurence of the Submitted_data and the others are computed for each entries as 1 so my purpose even if we found duplication for the message /email/campaign id compute the occurence as 1 so the overall total will be different ..
but the issue here in this query that the occurence of the Submitted_data and the others are computed for each entries as 1 so my purpose even if we found duplication for the message /email/campaign id compute the occurence as 1 so the overall total will be different ..
Sorry but submitted_data is not duplicated but it is aggregated.
Aggregation (summarize) work always on top of group(s) and aggregation functions (count, sum, min, max, avg, etc...) works inside these groups. Group is distinct value of a column, or distinct values of a set of column. In your case the group is composed by the following set of columns: id, name, id, email, first_name, last_name, position.
Any different values of these columns creates a new group for your query.
So submitted_data is not duplicated but you have 4 groups (in your example) and nothing happens just calculated a SUM of submitted data on those 4 groups. If you want to see them in 1 row then, you have to remove columns from group set. For example:
Aggregation (summarize) work always on top of group(s) and aggregation functions (count, sum, min, max, avg, etc...) works inside these groups. Group is distinct value of a column, or distinct values of a set of column. In your case the group is composed by the following set of columns: id, name, id, email, first_name, last_name, position.
Any different values of these columns creates a new group for your query.
So submitted_data is not duplicated but you have 4 groups (in your example) and nothing happens just calculated a SUM of submitted data on those 4 groups. If you want to see them in 1 row then, you have to remove columns from group set. For example:
select
[groups].[id]
, [groups].[name]
, sum(case when [events].[message] = 'Email Sent' then 1 else 0 end) as 'email_sent'
, sum(case when [events].[message] = 'Email Opened' then 1 else 0 end) as 'email_opened'
, sign(sum(case when [events].[message] = 'Clicked Link' then 1 else 0 end)) as 'clicked_link'
, sum(case when [events].[message] = 'Submitted Data' then 1 else 0 end) as 'submitted_data'
, max(case when [events].[message] = 'Submitted Data' then [events].time else null end) as 'LastSubmittedDatatime'
, max(case when [events].[message] = 'Email Reported' then [events].time else null end) as 'LastReportedEmailtime'
from [groups]
left join [group_targets] as [gt] on [groups].[id] = [gt].[group_id]
left join [targets] on [targets].[id] = [gt].[target_id]
left join [events] on [events].[email] = [targets].[email]
group by [groups].[id], [groups].[name]
If you want to ask more then create a new question because it is already answered.
ASKER
Thank you a lot Máté
Please can you help me in this question
https://www.experts-exchange.com/questions/29182340/sqlite-aggregate-function-sum.html
Please can you help me in this question
https://www.experts-exchange.com/questions/29182340/sqlite-aggregate-function-sum.html
Well SQLite is not my profession but I try.
ASKER
mailto:xxx@xyz.com/1/Submitted Data
mailto:xxx@xyz.com/1/Submitted Data
mailto:xxx@xyz.com/2/Submited Data
I want the result will be like that
email/total Submitted Data
mailto:xxx@xyz.com/2
why is 2 ? because if there is duplicated of Submitted Data in the same campaign compute it as only 1 occurence if not found the total will be 0