I need to count X where:

  X <= 1    : GREEN Column
  X 1 ~ 1.5 : YELLOW Column
  X > 1.5    : RED Column

Understand?
The query that i write is this:

SELECT FKID,
           COUNT(IF(X <= 1 THEN TRUE ELSE FALSE)) AS GREENX,
           COUNT(IF(X > 1 AND X <= 1.5 THEN TRUE ELSE FALSE)) AS YELLOWX,
           COUNT(IF(X > 1.5 THEN TRUE ELSE FALSE)) AS REDX
FROM TBPOINTS
WHERE FKID IN (SELECT ID FROM TBMEASUREMENTS ORDER BY ID DESC TOP 15)
GROUP BY FKID

The results are identical on all columns.. HELP!!!

Eduardo


Try this

SELECT FKID,
           SUM(IF(X <= 1 THEN 1 ELSE 0)) AS GREENX,
           SUM(IF(X > 1 AND X <= 1.5 THEN 1 ELSE 0)) AS YELLOWX,
           SUM(IF(X > 1.5 THEN 1 ELSE 0)) AS REDX
FROM TBPOINTS
WHERE FKID IN (SELECT ID FROM TBMEASUREMENTS ORDER BY ID DESC TOP 15)
GROUP BY FKID

Roy Lambert

Eduardo,

Roy's query will work, as will this:

SELECT FKID,
           COUNT(IF(X <= 1 THEN TRUE ELSE NULL)) AS GREENX,
           COUNT(IF(X > 1 AND X <= 1.5 THEN TRUE ELSE NULL)) AS YELLOWX,
           COUNT(IF(X > 1.5 THEN TRUE ELSE NULL)) AS REDX
FROM TBPOINTS
WHERE FKID IN (SELECT ID FROM TBMEASUREMENTS ORDER BY ID DESC TOP 15)
GROUP BY FKID

Also, please post any attachments in the Binaries newsgroup instead of
directly here.

--
Tim Young
Elevate Software
www.elevatesoft.com

Eduardo Pereira

Reading your e-mail address, I realize you are from Brazil. Am I right ?
If so, we can exchange some informations about DBISAM, I am DBISAM user
since 1998.

Regards.

Eduardo