CODEWITHSUNDEEP
mysql
chrislondon
chrislondon

Reputation: 12031

MIN( ) with conditions? MySQL

So I have a table with a few columns and I'm doing a GROUP BY. In addition to getting other columns I want to get the MIN() value of one column when the other column is NULL. Here's what I have so far:

Table columns:

id        INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
table2_id INT(11) UNSIGNED NOT NULL,
iteration INT(11) UNSIGNED,
timestamp INT(11) UNSIGNED NOT NULL

SQL:

SELECT
    table2_id,
    COUNT(id) as total,
    COUNT(iteration) as completed,

    # I want the MIN(timestamp) but only when
    # iteration IS NULL
    MIN(timestamp) as next_incomplete
FROM
    table1
GROUP BY
    table2_id

Upvotes: 6

Views: 6941

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Use this for the MIN expression:

MIN(CASE WHEN iteration IS NULL THEN timestamp END) as next_incomplete

Upvotes: 16

Related Questions