Click to See Complete Forum and Search --> : HOW to count records in database??
NinaWilliam
05-31-2005, 05:35 AM
in my database each doctor has multiple slips (one to many)..i want to cout the total
number of slips for each doctor and display it in the datagrid...in a culomn called
"Uncompleted Slips".. so it will display the total number of slips near the doctor name
How can i do that??
SQL Server 2000
PeOfEo
05-31-2005, 11:39 AM
One way would be to populate a dataset and then use rows.count
NinaWilliam
06-01-2005, 12:16 AM
is there a way to count without using the DataSet??
MikeFlyer
06-01-2005, 05:01 AM
You can use this select statement
select count(*) as Counts from [Table Name] where [Condition]
and then catch the column Counts
NinaWilliam
06-01-2005, 06:44 AM
I tried it..but it is giving me the total of all slips..and i want it to give me the total of slips for each doctor...
how??
MikeFlyer
06-01-2005, 07:05 AM
use where doctorid=[The ID Passed] for example
select count(*) as Counts from slips where DoctorID=2
it will return the total number of slips for only the doctor id selected.
NinaWilliam
06-02-2005, 12:00 AM
what if doctorId is not selected.. what i want to view the totalt of slips in a new column??
PeOfEo
06-02-2005, 01:44 AM
use an if statement then to use a different select statement for no doctors.
gowtham
06-07-2005, 06:21 AM
try using group by clause in the select statement grouping by the name of the doctor
NinaWilliam
06-13-2005, 12:11 AM
thank you very much.. it worked :)
If you want to display all the doctor names and the count of their slips you will have to do a join on the two tables.
SELECT d.<doctorNameField>, COUNT(s.*) AS "Uncompleted Slips"
FROM <doctorTable> d, <slipsTable> s
WHERE d.<doctorPrimaryKey> = s.<doctorForeignKey>
The last line joins the tables.