Hi;

I am trying to create some quick counts in a MySQL Query.

The following works just fine:

SELECT YEAR,
SUM(IF(PROGRAM='program1',1,0)) AS P1,
SUM(IF(PROGRAM='program2',1,0)) AS P2,
COUNT(*) AS TOTAL
FROM PROGRAMS

WHERE STATUS='ACTIVE' OR STATUS='COMPLETED'
GROUP BY YEAR
ORDER BY YEAR

BUT

I would like to have a condition that gives me a SUM column where PROGRAM does not equal EITHER program1 or program2. Is there a way to combine two or more "NOT EQUAL TO" type conditions in a SUM(IF(),1,0) AS OTHER section of the field list? I can't seem to get the syntax right and can't seem to find docs on such statements.

this does not work:
SUM((IF(PROGRAM='program1') and IF(PROGRAM='program2')),1,0) AS OTHER,
nor does
SUM((IF(PROGRAM='program1') and (PROGRAM='program2')),1,0) AS OTHER,
nor does
SUM((IF(PROGRAM='program1' and PROGRAM='program2')),1,0) AS OTHER,

Thanks.

    This seems to work:

    SELECT YEAR,
    SUM(IF(PROGRAM='program1',1,0)) AS P1,
    SUM(IF(PROGRAM='program2',1,0)) AS P2,

    SUM(CASE WHEN PROGRAM != 'program1' AND PROGRAM != 'program2' THEN 1 ELSE 0 END) AS OTHER,

    COUNT(*) AS TOTAL
    FROM PROGRAMS
    WHERE STATUS='ACTIVE' OR STATUS='COMPLETED'
    GROUP BY YEAR
    ORDER BY YEAR

    Still, just for the sake of consistency (the hobgoblin of little minds, such as my own) it would be nice to know if the SUM(if(),1,0) route could handle multiple conditions.

      Write a Reply...