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 2000One way would be to populate a dataset and then use rows.countis there a way to count without using the DataSet??You can use this select statement
select count(*) as Counts from [Table Name] where [Condition]
and then catch the column CountsI 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??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.what if doctorId is not selected.. what i want to view the totalt of slips in a new column??use an if statement then to use a different select statement for no doctors.try using group by clause in the select statement grouping by the name of the doctorthank 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.
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 2000One way would be to populate a dataset and then use rows.countis there a way to count without using the DataSet??You can use this select statement
select count(*) as Counts from [Table Name] where [Condition]
and then catch the column CountsI 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??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.what if doctorId is not selected.. what i want to view the totalt of slips in a new column??use an if statement then to use a different select statement for no doctors.try using group by clause in the select statement grouping by the name of the doctorthank 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.