?
Solved

Trying to count number of foreign keys

Posted on 2005-05-01
6
Medium Priority
?
264 Views
Last Modified: 2010-03-19
I have a table with Hospitals and I need to display # number of people assigned as I display a listing of these hospitals.

I tried to redo the query but I need to figure out how to write the sum( ProviderID's ) that match to each of the hospital rows. I know the query below kind of works, but it isn't summing them together.

SELECT     Hospital.HospitalID, Hospital.HospitalName, Provider.HospitalID, Provider.ProviderID
FROM         Hospital INNER JOIN
                      Provider ON Hospital.HospitalID = Provider.HospitalID

Heres what I need to display:

ID | HospitalName | # Providers
1     Hosp A             29
2     Hosp B             0
3     Hosp C             4

0
Comment
Question by:mcsolas
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:nagki
ID: 13904412
SELECT     Hospital.HospitalID, Hospital.HospitalName, count(Provider.ProviderID)
FROM         Hospital INNER JOIN
                      Provider ON Hospital.HospitalID = Provider.HospitalID
group by Hospital.HospitalID,Hospital.HospitalName
0
 
LVL 1

Author Comment

by:mcsolas
ID: 13904459
The only problem with this is that I need to display all hospitals, even those with no providers. This works right but it merges those with a count of 0 out of the equation.
0
 
LVL 1

Author Comment

by:mcsolas
ID: 13904567
I have a working version of this query now. I am just wondering if there is a cleaner ( shorter ) way of doing this. Since 1 selects only those with the HospitalID's defined in the Provider table, the union and the SELECT ..,0 grabs the others.

(
SELECT Hospital.HospitalID,Hospital.HospitalName,count(*) AS ProNum FROM Hospital, Provider
WHERE Provider.HospitalID=Hospital.HospitalID GROUP BY Hospital.HospitalID,Hospital.HospitalName
)
UNION
(
SELECT Hospital.HospitalID,Hospital.HospitalName,0 AS ProNum FROM Hospital, Provider
WHERE 0=0 And Hospital.HospitalID not in
(
SELECT Hospital.HospitalID FROM Hospital,Provider WHERE Provider.HospitalID=Hospital.HospitalID GROUP BY Hospital.HospitalID
)
)
ORDER BY Hospital.HospitalName ASC


=-=-=-=-=-=-=-=-=-

I was thinking you could do this a different way:
SELECT     Hospital.HospitalID, Hospital.HospitalName FROM Hospital

then append a column subquery that just adds the count relating to each row after this one... any ideas?
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 13905519
Use a LEFT JOIN, as in:

SELECT     Hospital.HospitalID, Hospital.HospitalName, Count(*) As '# Providers'
FROM       Hospital
               LEFT JOIN Provider ON Hospital.HospitalID = Provider.HospitalID
GROUP BY Hospital.HospitalID, Hospital.HospitalName
0
 
LVL 1

Author Comment

by:mcsolas
ID: 13906831
Thanks for the feedback. Just curious about 1 last point:

Count(*) As '# Providers'

This table has a lot of fields defined. Would there be any performance increase using:

Count(ProviderID) As '# Providers'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13909008
>>Would there be any performance increase using:

Count(ProviderID) As '# Providers'<<
No.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question