[SOLVED] Count number of columns which = "Y" for selected rows?

[matthewra]

Hi,

Is it possible to write a query

which counts how many of the columns (named a,b,c,d,e,f,g) have value "Y"?

 

Ideally I would get

select fldAREA, HOWMANYYS from tblDATA GROUP BY fldAREA

HOWMANYYS would be +1 for each of the fields A,b,c,d,e,f,g have value "Y" in that record.

thanks

matthew@kleinosky.com

[matthewra]

I can write

select  if(org_trainp1pres="Y", 1,0) as t1, org_trainp1vped="Y" as t2,  org_trainp1vppr="Y" as t3,

org_area, org_div, org_clubnum  from d60org where org_area <> ""  and org_clubnum <> ''

order by org_div , org_area

 

But I want a result which is the sum of t1+t2+t3

[Daniel0]

Your question doesn't make much sense. Could you provide some more information (see: http://www.phpfreaks.com/forums/index.php/topic,129174.0.html)?

[kickstart]

Hi

 

Not quite sure what you want.

 

First guess is that you want to know each row and a count of how many of the columns for each row contain "Y". This would do it.

 

SELECT (IF(a = 'Y', 1 , 0) + IF(b = 'Y' , 1 , 0) + IF(c = 'Y' , 1 , 0) + IF(d = 'Y' , 1 , 0) + IF(e = 'Y' , 1 , 0) + IF(f = 'Y' , 1 , 0)) AS HOWMANYYS
from tblDATA 

 

If you want to know how many rows have a each have a Y in the various columns:-

 

SELECT 'a' AS fldAREA, COUNT(*)
FROM tblDATA
WHERE a = 'Y'
UNION
SELECT 'b' AS fldAREA, COUNT(*)
FROM tblDATA
WHERE b = 'Y'
UNION
SELECT 'c' AS fldAREA, COUNT(*)
FROM tblDATA
WHERE c = 'Y'
UNION
SELECT 'd' AS fldAREA, COUNT(*)
FROM tblDATA
WHERE d = 'Y'
UNION
SELECT 'e' AS fldAREA, COUNT(*)
FROM tblDATA
WHERE e = 'Y'
UNION
SELECT 'f' AS fldAREA, COUNT(*)
FROM tblDATA
WHERE f = 'Y'

 

Neither seem to point to a particularly logical table.

 

All the best

 

Keith

[matthewra]

SELECT (IF(a = 'Y', 1 , 0) + IF(b = 'Y' , 1 , 0) + IF(c = 'Y' , 1 , 0) + IF(d = 'Y' , 1 , 0) + IF(e = 'Y' , 1 , 0) + IF(f = 'Y' , 1 , 0)) AS HOWMANYYS

from tblDATA

 

is exactly what I need. THANKS!