Goderaftw Goderaftw - 5 months ago 13
MySQL Question

MYSQL SUM function with and inside

I'm doing a query to get several counts at once for a left navigation. I'm summing the number of rows that match each condition and passing that number to the DOM for the left navigation, however two of the SUMS are more complicated than the others and are returning 0 even though I know there are rows in the DB that match the expression.

$processingcounts = $second->select(DB::raw('
SUM(scheduled_in != "") as processingPreRepairs,
SUM(in_arrival = 1) as processingNewArrivals,
SUM(delivery_complete = 1) as processingDelivered,
SUM(supplement = 1) as processingSupplement,
SUM(scheduled_in >= '.$start.' and scheduled_in <= '.$end.') as processingCurrentWeek,
SUM(scheduled_in >= '.$nextStart.' and scheduled_in <= '.$nextEnd.') as processingNextWeek,
SUM(file_location = "SCHEDULED") as processingScheduledList,
SUM(file_location = "IN-SHOP") as processingInShop,
SUM(file_location = "OFFICE") as processingOffice
'))->first();


All the other SUMS are returning the correct values, but the two SUMS I call "processingCurrentWeek" and "processingNextWeek" are returing 0, though they should be returning 2 and 3 respectively. My question is can you use SUM like I do below with basically wheres inside of them.

EDIT:

Here is the query output with the dates, when I dump the laravel query.

SUM(scheduled_in != "") as processingPreRepairs,
SUM(in_arrival = 1) as processingNewArrivals,
SUM(delivery_complete = 1) as processingDelivered,
SUM(supplement = 1) as processingSupplement,
SUM(scheduled_in >= 2016-06-13 and scheduled_in <= 2016-06-17) as processingCurrentWeek,
SUM(scheduled_in >= 2016-06-20 and scheduled_in <= 2016-06-24) as processingNextWeek,
SUM(file_location = "SCHEDULED") as processingScheduledList,
SUM(file_location = "IN-SHOP") as processingInShop,
SUM(file_location = "OFFICE") as processingOffice

Answer

Dates must be wrapped as strings. They interpreted as expressions now: 2016-06-10 = 2000.

You can to use placeholders:

$processingcounts = $second->select(' 
    SUM(scheduled_in != "") as processingPreRepairs,
    SUM(in_arrival = 1) as processingNewArrivals, 
    SUM(delivery_complete = 1) as processingDelivered, 
    SUM(supplement = 1) as processingSupplement, 
    SUM(scheduled_in >= ? and scheduled_in <= ?) as processingCurrentWeek, 
    SUM(scheduled_in >= ? and scheduled_in <= ?) as processingNextWeek, 
    SUM(file_location = "SCHEDULED") as processingScheduledList, 
    SUM(file_location = "IN-SHOP") as processingInShop, 
    SUM(file_location = "OFFICE") as processingOffice 
    ', [$start, $end, $nextStart, $nextEnd])->first();
Comments