Simple SQL COUNT question

[johnsmith153]

SELECT COUNT(*) obviously counts all records matching the WHERE clause,

 

however...

 

Field1 / Field2

1 / A

  / A

1 / B

1 / A

 

I want to count all records where Field2 is "A", but if Field1 is set to "1" then that record counts as half. So above would return a result of 2 (one and two halfs = 2)

 

I'm sure SQL can do this without the need to perfrom PHP calculations.

 

Hope this makes sense. Thanks in advance.

[spfoonnewb]

I believe what you are looking for is COUNT(IF()).

 

COUNT(IF(Field1 = 1, 0.5, 1)) WHERE Field2 = 'A'

 

This has not been tested, but something like it should work.

 

You may need the SUM() function (instead of COUNT) for what you are looking for.

 

If the field is literally null, IFNULL may be faster.