By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

SQL Counting number of non-distinct rows?

P: n/a
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

May 3 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

I think this is what you want
SELECT R1,COUNT(*)
FROM(
SELECT DISTINCT R1,R2,R3
FROM mytable
) X
GROUP BY R1

May 3 '06 #2

P: n/a
"David" <da************@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
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


Look at the "GROUP BY" clause.
May 3 '06 #3

P: n/a
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;

May 3 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.