need help fro sql query

[desi90]

hi,
i have following table data

city region issue value
c1 north i1 y
c1 north i2 y
c2 north i1 n
c2 north i2 y
c3 south i1 y
c3 south i2 n
c4 east i1 n
c4 east i2 n

I would like to create a report from this table which looks like this-
region issue count(yes)
north i1 2
north i2 1
south i1 1
south i2 0
east i1 0
east i2 0

any help is appreciated.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT region, issue, abs(sum([value])) as yeses
FROM table_name
GROUP BY region, issue

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkB6E4echKqOuFEgEQJahgCg1IpkOt+N3mEBuUZocDh8Az IkTgMAn2qv
IKRhsuJrJYehDeuYAogB5nUo
=c5yZ
-----END PGP SIGNATURE-----
desi90 wrote:

hi,
i have following table data

city region issue value
c1 north i1 y
c1 north i2 y
c2 north i1 n
c2 north i2 y
c3 south i1 y
c3 south i2 n
c4 east i1 n
c4 east i2 n

I would like to create a report from this table which looks like this-
region issue count(yes)
north i1 2
north i2 1
south i1 1
south i2 0
east i1 0
east i2 0

any help is appreciated.

[pietlinden]

worked when I did this...

SELECT region, issue, abs(sum(iif([value]="y",1,0))) as yeses
FROM table_name
GROUP BY region, issue;

I had to explicitly convert "y" to 1...