Laravel Query to Group by two different dates

Question

Assuming I am searching with input variables: $from :01-01-2016 $to : 03-12-2016

Here is my current table in the table:(outreach_links)

created_at   start_date     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      NULL       P
2016-12-03      NULL       P
2016-12-03      NULL       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

The logic is I want to group Status = "P" by created_at And I want to group Status="A" by start_date ? how can I accomplish this in query?

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) only is wrong:

    Month          P        A 
    ---------------------------
    Dec/2016       3        4

Here is the output of my query when I do >> var_dump();

array(1) {
  [0]=>
  object(stdClass)#369 (6) {
    ["pp"]=>
    string(1) "3"
    ["aa"]=>
    string(1) "4"
    ["cc"]=>
    string(4) "0.00"
    ["month"]=>
    string(2) "12"
    ["month_name"]=>
    string(8) "December"
    ["year"]=>
    string(4) "2016"
  }
}

Can you help me to modify the query?

Thanks

any other help plz?

user3150060
– user3150060

2016-12-02 00:25:07 +00:00
Commented Dec 2, 2016 at 0:25 — user3150060
– user3150060, Commented Dec 2, 2016 at 0:25

Darshan Mehta · Accepted Answer · 2016-12-02 07:50:19Z

1

This would do the job:

select concat(monthname(date), '/', year) as Month,
(select count(*) from test where year(created_at) = year and month(created_at) = month and status = 'P') as P,
(select count(*) from test where year(start_at) = year and month(start_at) = month and status = 'A') as A
from
(select year(created_at) as year, month(created_at) as month, created_at as date
from test where created_at >= ? and created_at <= ?
union
select year(start_at) as year, month(start_at) as month, start_at as date
from test where start_at >= ? and start_at <= ?) t1
group by year, month
order by date

Basically, it UNIONs all the dates from this table and does a group by on top of that.

Here is the SQL Fiddle.

edited Dec 2, 2016 at 7:50

answered Dec 1, 2016 at 23:18

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3150060 Over a year ago

is there away I an inner join another table with this query??

user3150060 Over a year ago

where is the date $from and $to input?

user3150060 Over a year ago

his is not working corectly because the $from and $to are variables and they could change how can i add those to make i work

Darshan Mehta Over a year ago

You can add $from and $to inputs in the inner queries to restrict the dates. Let me edit the answer.

Darshan Mehta Over a year ago

@user3150060 I have added the dates in the inner queries. Also, you can inner join another table with this query by enclosing this query in a from close. Have a look at this : joyofdata.de/blog/…

|

Collectives™ on Stack Overflow

Laravel Query to Group by two different dates

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related