Assuming I am searching from :01-01-2016 to : 03-12-2016 Here is my current table in the table:
created_at start_at status
---------------------------------
2016-12-01 2016-01-01 A
2016-12-02 2016-02-01 A
2016-12-03 2016-03-01 A
2016-12-03 2016-04-01 A
2016-12-03 2016-12-01 P
2016-12-03 2016-12-01 P
2016-12-03 2016-12-01 P
I would like to output like this after the query >>
Month P A
---------------------------
Jan/2016 0 1
Feb/2016 0 1
March/2016 0 1
April/2016 0 1
Dec/2016 3 0
Here is my current laravel 5.2 query:
DB::select(
DB::raw('select
sum(case when outreach_links.status="P" and outreach_links.created_at >= ? and outreach_links.created_at <=? then 1 else 0 end) as pp,
sum(case when outreach_links.status="A" and outreach_links.start_date >= ? and outreach_links.start_date <=? then 1 else 0 end) as aa,
sum(case when outreach_links.status="A" then outreach_links.cost else 0 end) as cc,
MONTH(outreach_links.created_at) month,
MONTHNAME(outreach_links.created_at) month_name,
YEAR(outreach_links.created_at) year from outreach
inner join outreach_links on outreach.id = outreach_links.outreach_id
where outreach.profile_id=?
group by month, year'),
[$from, $to, $from, $to, $pro_id]
);
the output for this one since I am grouping by (created_at) is only this: ( how can I group all status that is "A" by the start_at not created_at?)
Month P A
---------------------------
Dec/2016 3 4
Can you help me to modify the query?
Thanks
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire