query

[krisworld]

I have 2 fields in my table
F1& F2

F1 F2
--------------
a aa
a aa
a bb
a cc
b aa
b bb
c cc
c cc


i need output as

a aa 2
a bb 1
a cc 1
b aa 1
b bb 1
c cc 2


I am bit confused for the query
can any give me some idea...

 

[Rob Parker]

Try this:

SELECT F1, F2, Count(F1) AS CountF1F2
FROM MyTableName GROUP BY F1, F2;

HTH,

Rob

 

[Jason Lepack]

Add one of the fields twice, count one of them and group by the other
two. See SQL below:

SELECT F1, F2, Count(F1) AS Total
FROM Table3
GROUP BY F1, F2;

Cheers,
Jason Lepack

 

[krisworld]

Thanks Rob & Jason
I made the query as follows

SELECT F1,
(select count(*) from table1 t1 where t1.F1=t.F1 and F2="aa") AS AA,
(select count(*) from table1 t1 where t1.F1=t.F1 and F2="bb") AS BB,
(select count(*) from table1 t1 where t1.F1=t.F1 and F2="cc") AS CC
FROM table1 AS t
GROUP BY F1;

 

[Michel Walsh]

Sounds you made a crosstab the hard way:


TRANSFORM COUNT(*)
SELECT f1
FROM table1
GROUP BY f2
PIVOT f2 IN('aa', 'bb', 'cc')



would also do the job, and probably in a more versatile way, if not also
faster.


Vanderghast, Access MVP