CODEWITHSUNDEEP
phpmysqlsql
Brandon Shega
Brandon Shega

Reputation: 769

Is it possible to use an if statement in SQL?

So basically I have a database that holds queued up emails for an email campaign.

The emails can have a bunch of different statuses and if the emails fail, they are reattempted.

The table looks like

id       email              status      attempts

0       [email protected]       failed         4

0       [email protected]       success        2

0       [email protected]       success        1

0       [email protected]       failed         2

0       [email protected]       new            0

I want to get a count of all of the emails that have sent so far or have too many attempts.

In other words count all of the emails where status != 'new', but if status = 'failed' only count the ones with an attempt of 4 or higher.

Is this possible with an SQL query, or do I have to do this in PHP?

Thanks in advance!

Upvotes: 1

Views: 354

Answers (5)

Federico J.
Federico J.

Reputation: 15902

Yes, it's possible.

You may add a where condition, and sum all the results you need:

SELECT 
  SUM( IF( STATUS = 'failed' AND attempts >= 4, 1, 0 ) ) AS total_failed 
, SUM( 1 ) AS total_olds
FROM my_table
WHERE STATUS != 'new'

The sum only will add the value 1 if you match the IF condition. In MySQL, if the IF clause matches, applies the first part, else the second (http://dev.mysql.com/doc/refman/5.0/en/if.html) Here, we say "hey, if the condition is matched, sum 1 row, else, do nothing", and this way you get all the results in one row, XD.

And as we're only selecting not 'new' rows, only adding 1 (SUM(1)) we'll get the value of all the rows matched.

Upvotes: 1

Suchit kumar
Suchit kumar

Reputation: 11859

   add a where clause to your select as:
    where status ="success" or status = "failed" and attempts >= 4

no need to add status !='new' if you don't want to select on this criteria.

Upvotes: 0

smzsmzsmz
smzsmzsmz

Reputation: 44

Try this query below:

SELECT 
COUNT(email)


FROM 'your table'

WHERE 
STATUS = 'success'
OR 
(STATUS = 'failed' AND attempts >=4)

Upvotes: 0

ruakh
ruakh

Reputation: 183311

You could write

WHERE status = 'success'
   OR status = 'failed' AND attempts >= 4

Upvotes: 3

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can write your query using sum with expression,in Mysql it is evaluated as boolean,So first sum with expression will give you the count for email where status is not equal to failed and sum with expression will give you the count where status is failed and attempts are more than 3 and then add up the results of both expression will give you the desired count

select email
sum(status <> 'failed') + sum(status = 'failed' and attempts > 3)  as your_count
from table   
where status <> 'new'

Upvotes: 0

Related Questions