473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Counting number of non-distinct rows?

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
3 4987

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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: A Seel | last post by:
COUNTING NUMBER OF SELECTS MADE table mytable { id, data, hits }
1
by: Sourabh | last post by:
Hello, I am trying to write a VB.NET monitoring application for a MS SQL server. For that I need to know the following: 1. How do I count the number of read/write accesses per database on the...
0
by: Darren | last post by:
Hello, I am trying to design an ASP system which will submit the contents of a feedback form to a partner, based on a daily maximum number of feedback forms per day. For example, if...
14
by: ranjmis | last post by:
Hi all, Below is the code wherein I am initializing double dimentional array inside main with string literals. Now I want to display the strings using a function call to which I just want to...
16
by: sophie_newbie | last post by:
I have a list a little something like this: StringA StringC StringB StringA StringC StringD StringA ....
4
by: Nishant | last post by:
Hi all, i want to find a mat lab algorithm in order to count number of zeros in any given matrix for eg Given Matrix A: 1 0 1 1 0 0 1 1
1
by: KelHemp | last post by:
I'm trying to count the number of records within a form (and report) that have the same number in the "LNumber" field. If I had a set number I wanted, I'd just run a single query, but the number...
3
by: bupanda | last post by:
I want to write a code for one of my command buttons (cmdCalculate) and that it shows the number of times it has been clicked in the lblGroupCount. I went through some books and I found this two...
1
by: palithawarnasiri | last post by:
In an access form I need to count number of records in the running month. Otherwise I need increamental number in a field mentioning serial number. First day of the month first sample should be 1...
8
by: Mike P | last post by:
What would be the best way of counting the number of users who are currently logged on to a website? I am making the users login against a database of valid users. Would the best way be to add a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.