Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Because I am old school, I am in the habit of first writing my queries in SQL, and then converting them to QB. So, the below works fine. The objective is to count the number of child_id for every given date between start and end.
SELECT
date,
COUNT(child_id)
FROM
attendance
WHERE (date BETWEEN '2021-01-01' AND '2021-01-12 ')
GROUP BY date;
However, when I attempt to move it to QB based on advice from SO,
( https://stackoverflow.com/questions/13223512/how-to-select-count-with-laravels-fluent-query-builder )
I get an error:
$dates = DB::table('attendance')
->select(array('date', DB::raw('COUNT(child_id)')))
->where('date', '>=', $this->startDate)
->where('date', '<=', $this->endDate)
->groupBy('date')
->get();
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count' in 'field list' (SQL: select `date`, `count` from `attendance` where `date` >= 2021-01-01 and `date` <= 2021-01-12 order by `date` desc)
Where am I going wrong?
You can use selectRaw instead:
$dates = DB::table('attendance')
->selectRaw('date, COUNT(child_id)')
->whereBetween('date', [$this->startDate, $this->endDate])
->groupBy('date')
->get();
EDIT missing closing ] in whereBetween
hi,
Try to use like this
$dates = DB::table('attendance')
->select(DB::raw('date, COUNT(child_id)'))
->where('date', '>=', $this->startDate)
->where('date', '<=', $this->endDate)
->groupBy('date')
->get();
Thank you both for your rapid replies. I tried both versions and in both cases I am getting the same error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'count' in 'field list' (SQL: select `date`, `count` from `attendance` where `date` >= 2021-01-01 and `date` <= 2021-01-12 order by `date` desc)
It's not same query what they gave you.
Are you running a similar query elsewhere which is resulting in this exception message? There is an ORDER BY in the exceptional query - none in the example I gave.
My bad, I took the orderBy out and I still get the same error. Laravel has an issue with count
Ok, it is solved. My bad - I had a reference to an alternative function which was creating a conflict. Now between @tykus solution and @adiputra22 solutions I got an error on @tykus code saying I needed an array arond the count, so I went with @adiputra22.
I thank you both for your help, and sorry I took so long to fix it.
@michaloravec @tykus I understand that is true. However, late last night, when I finally got it working, I could not see where to put the array PHPStorm was asking for.
Thanks!
@vincej Always check documentation.
Please or to participate in this conversation.