SQL Counting number of non-distinct rows?

[David]

Hi, I have a table that for ease has this data in:

R1, R2, R....z
---------------------
A | 12
A | 22
A | 30
B | 0
B | -1
B | -3
C | 100

I want to generate a table for each distinct row in R1, gives a count
of all the rows with data corresponding

For the above table I would get

A | 3
B | 3
C | 1

Im probably being stupid but cannot see this at the moment... please
help.

Thanks

[markc600@hotmail.com]

Re: SQL Counting number of non-distinct rows?


I think this is what you want


SELECT R1,COUNT(*)
FROM(
SELECT DISTINCT R1,R2,R3
FROM mytable
) X
GROUP BY R1

[Ronnie Chee]

Re: SQL Counting number of non-distinct rows?

"David" <david.goodyear @gmail.com> wrote in message
news:1146673034 .443139.173600@ j33g2000cwa.goo glegroups.com.. .[color=blue]
> Hi, I have a table that for ease has this data in:
>
> R1, R2, R....z
> ---------------------
> A | 12
> A | 22
> A | 30
> B | 0
> B | -1
> B | -3
> C | 100
>
> I want to generate a table for each distinct row in R1, gives a count
> of all the rows with data corresponding
>
> For the above table I would get
>
> A | 3
> B | 3
> C | 1
>
> Im probably being stupid but cannot see this at the moment... please
> help.
>
> Thanks
>[/color]

Look at the "GROUP BY" clause.

[--CELKO--]

Re: SQL Counting number of non-distinct rows?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. There is no key here, for example, so it is BY
DEFINITION not a table at all!

SELECT r1, COUNT(*) AS r1_tally
FROM Foobar
GROUP BY r1;