grouping by month issue

[joel24]

I'm having some trouble grouping by month

 

I have a database with payments to contractors which are attributed a start and finish as a unix timestamp, an hourly rate and a confirmed column of true or false depending on whether the contractor finished their work etc

 

payments

contractorID, paymentID, startTime(unix), finishTime(unix), rate(decimal(5,2), confirmed(tinyInt(1))

 

contractors

contractorID, contractorName, address, phone, mobile etc etc

 

Now i'm creating a report and need to group the payments by month, displaying the monthly payment amount where the 'pay' column is true, I can do this but as I am grouping by contractorID then month if any of the confirmed columns in that month for that contractor are false then no values are displayed as it is grouping by month...

 

This is the sql statement I've been mucking around with

SELECT
c.contractorName, c.contractorID,
DATE_FORMAT(from_unixtime(startTime), '%M %Y') AS month,
IF(confirmed=1,round(sum((endTime-startTime)/60/60),2),0) AS hoursWorked,
IF(confirmed=1,round(sum((endTime-startTime)/60/60) * rate,2),0) AS pay

FROM payments p 
JOIN contractors c ON c.contractorID = p.contractorID

GROUP BY p.contractorID, month(from_unixtime(start));

 

In short

IF(confirmed=1,round(sum((endTime-startTime)/60/60) * rate,2),0) AS pay

is not working if any 'confirmed' values for that month are false

 

 

[fenway]

That's because you can't use a non-aggregated column value and expect it to mean anything.

 

Why not simply filter them out in the WHERE clause?