CODEWITHSUNDEEP
mysqldatediff
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

DATEDIFF if a value IS NOT NULL

This is a follow-up of this question which i posted earlier.

I am calculating the SUM of DATEDIFF using following syntax

SUM(DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from)) / COUNT(DISTINCT e.company_id)

What i want now is to calculate SUM of DATEDIFF only if e.time_period_from is NOT NULL

I tried following query.

SUM(IF(e.time_period_from IS NOT NULL), DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from), 0) / COUNT(DISTINCT e.company_id)

This gives me SQL syntax error.

How to go about it ?

UPDATE :

Here is my full MySQL Query

SELECT
    SQL_CALC_FOUND_ROWS
    u.id,
    u.name,
    u.email,
    COUNT(DISTINCT(question_id)) as number_of_answered_questions,
    (SELECT option_id FROM answer WHERE user_id = u.id GROUP BY option_id ORDER BY COUNT(option_id) DESC LIMIT 1) as option_id,
    SUM(IF(e.time_period_from IS NOT NULL), DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from), 0) / COUNT(DISTINCT e.company_id) AS tenure_in_days
FROM
    user u
LEFT JOIN
    role r ON (r.id = u.role_id)
LEFT JOIN
    answer a ON (a.user_id = u.id)
LEFT JOIN
    employment e ON (e.user_id = u.id)
WHERE
    r.slug = 'app_user'
GROUP BY
    u.id
LIMIT
    0, 10

As you see this is for a sub select i cannot put a where condition outside of it.

And here is the error i get.

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from), 0) / COUNT(D' at line 1"

Thanks.

Upvotes: 0

Views: 1666

Answers (2)

Vipin Jain
Vipin Jain

Reputation: 3756

i check your query error in this line

 SUM(IF(e.time_period_from IS NOT NULL), DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from), 0)

use this instead above line code 

 SUM(IF(e.time_period_from IS NOT NULL, DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from), 0));

Upvotes: 1

Kai Iskratsch
Kai Iskratsch

Reputation: 191

as in my comment before i think you just set brackets wrong what you want to get is this:

SUM(IF(e.time_period_from IS NOT NULL, DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from), 0))
/ COUNT(DISTINCT e.company_id)

Upvotes: 1

Related Questions