Programming Forums > Script Programming > Other Scripting Languages

taporctv · Jun 5th, 2007, 2:00 PM

Ok here's my problem. I ran a query that joined two tables. I used a WHERE statement to sort out the data. My problem is, how do I count the row in the sorted data table?

DaWei · Jun 5th, 2007, 2:40 PM

I don't understand your question. Do you mean that you want to know how many rows you have in the result? How to go to a specific row in the result? How to number the rows as you process them?

taporctv · Jun 5th, 2007, 2:54 PM

I want to know how many rows are returned in the result. Im using Access. I know that it says how many records are in the table at the bottom of the table. What I want to do is use the count function to determine how many rows were returned.

kruptof · Jun 5th, 2007, 3:09 PM

I think you would have to use the Group By clause, something similar to this:

(Toggle Plain Text)

select col1,count(*)
from table1 t1, table2 t2
where t1.id=t2.id and t1.name='john'
group by col1;

taporctv · Jun 5th, 2007, 3:49 PM

Ok I can count now, but my problem is since im joining three tables, im counting from the wrong table. The field i want to count is in the result of my query and in another table from the join. My count function is counting the field from the table i used in the join. How do i count the field in my query results.

Infinite Recursion · Jun 5th, 2007, 3:59 PM

Perhaps showing your query would help us help you.

taporctv · Jun 6th, 2007, 12:11 AM

[PHP]SELECT DISTINCT [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;[/PHP]

This returns 9 records, which is the right amount. Now when I use

[PHP]SELECT COUNT(DISTINCT [SV-5.NameA])[/PHP]

I get an error message. What's my problem. Without the DISTINCT I get a count of 48 records which is not right.

DaWei · Jun 6th, 2007, 6:07 AM

You have to apply COUNT to the same query that provides the correct results. Does Access not have a count function that you can merely pass the result to? Something like

(Toggle Plain Text)

result = SELECT DISTINCT  [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND  SFtoFD.NAMEB IS NULL;

records = num_rows (result)

I don't have Access on this system, so I can't check for you. Look at your docs.

taporctv · Jun 6th, 2007, 8:04 AM

That didn't work. I know it probably an easy solution that I cant think about.

xavier · Jun 6th, 2007, 8:55 AM

Apparently Access it's just weird

. It doesn't support count(distinct .. etc).

You could do this : http://allenbrowne.com/ser-66.html.
Or, you could Have an inner select , and count that:

 sql Syntax (Toggle Plain Text) 
SELECT count(*) FROM
(SELECT DISTINCT  [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND  SFtoFD.NAMEB IS NULL;) AS a
select count(*) from
(SELECT DISTINCT  [SV-5.NameA]
FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB
WHERE SFtoSV4.NAMEB IS NULL AND  SFtoFD.NAMEB IS NULL;) as a

Jun 5th, 2007, 2:00 PM	#1
taporctv Newbie Join Date: Mar 2006 Posts: 20 Rep Power: 0	Counting with SQL Ok here's my problem. I ran a query that joined two tables. I used a WHERE statement to sort out the data. My problem is, how do I count the row in the sorted data table?

Jun 5th, 2007, 2:40 PM	#2
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	I don't understand your question. Do you mean that you want to know how many rows you have in the result? How to go to a specific row in the result? How to number the rows as you process them? __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Jun 5th, 2007, 3:49 PM	#5
taporctv Newbie Join Date: Mar 2006 Posts: 20 Rep Power: 0	Ok I can count now, but my problem is since im joining three tables, im counting from the wrong table. The field i want to count is in the result of my query and in another table from the join. My count function is counting the field from the table i used in the join. How do i count the field in my query results. Last edited by taporctv; Jun 5th, 2007 at 4:00 PM.

Jun 5th, 2007, 3:59 PM	#6
Infinite Recursion Programming Guru Join Date: Jul 2004 Location: United States Posts: 3,467 Rep Power: 8	Perhaps showing your query would help us help you. __________________ http://jasonpowers.net "There are a thousand hacking at the branches of evil to one who is striking at the root."

Jun 6th, 2007, 6:07 AM	#8
DaWei Resident Grouch Join Date: Jun 2005 Posts: 6,453 Rep Power: 10	You have to apply COUNT to the same query that provides the correct results. Does Access not have a count function that you can merely pass the result to? Something like (Toggle Plain Text) result = SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL; records = num_rows (result) result = SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL; records = num_rows (result) I don't have Access on this system, so I can't check for you. Look at your docs. __________________ Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code. Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Jun 5th, 2007, 2:54 PM	#3
taporctv Newbie Join Date: Mar 2006 Posts: 20 Rep Power: 0	I want to know how many rows are returned in the result. Im using Access. I know that it says how many records are in the table at the bottom of the table. What I want to do is use the count function to determine how many rows were returned.

Jun 6th, 2007, 12:11 AM	#7
taporctv Newbie Join Date: Mar 2006 Posts: 20 Rep Power: 0	[PHP]SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;[/PHP] This returns 9 records, which is the right amount. Now when I use [PHP]SELECT COUNT(DISTINCT [SV-5.NameA])[/PHP] I get an error message. What's my problem. Without the DISTINCT I get a count of 48 records which is not right.

Jun 6th, 2007, 8:04 AM	#9
taporctv Newbie Join Date: Mar 2006 Posts: 20 Rep Power: 0	That didn't work. I know it probably an easy solution that I cant think about.

Jun 6th, 2007, 8:55 AM	#10
xavier Professional Programmer Join Date: Oct 2004 Location: .ro Posts: 383 Rep Power: 4	Apparently Access it's just weird . It doesn't support count(distinct .. etc). You could do this : http://allenbrowne.com/ser-66.html. Or, you could Have an inner select , and count that: sql Syntax (Toggle Plain Text) SELECT count() FROM (SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;) AS a select count() from (SELECT DISTINCT [SV-5.NameA] FROM ([SV-5] LEFT JOIN SFtoFD ON [SV-5].NAMEA = SFtoFD.NAMEB) LEFT JOIN SFtoSV4 ON [SV-5].NAMEA = SFtoSV4.NAMEB WHERE SFtoSV4.NAMEB IS NULL AND SFtoFD.NAMEB IS NULL;) as a __________________ Don't take life too seriously, it's not permanent !

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SQL Query Problem	King	Other Scripting Languages	4	Aug 8th, 2007 6:31 AM
counting digits	rwm	C++	25	Apr 11th, 2007 7:16 AM
Counting the Days....	Ghost	C#	2	Dec 8th, 2005 11:33 PM
Counting occurrence of numbers in C	stabule	C	6	Nov 15th, 2005 10:49 AM
Help with sorting and counting?	mmmm_strawberries	C++	8	Apr 3rd, 2005 6:47 PM

Programming Forums > Script Programming > Other Scripting Languages

Counting with SQL