asked on

SQL Syntax

I need to pull data out of a table which has many of the same accountIDs but I need to only get back a count of 1 for each account_id
Here is the syntax I have now and it is pulling back every instance of that accountID. So if accountID 123 is in the table 7 times I want to return 1

Select h.name, COUNT(*)
from document_requests dr
INNER JOIN document_request_accounts dra on dr.id = dra.document_request_id
INNER JOIN hospitals h on dr.hospital_id = h.id
GROUP BY h.name

Please help!!!

Patrick Matthews

Hello healthcheckinc,

Select h.name, 1 AS [Count ]
from document_requests dr
INNER JOIN document_request_accounts dra on dr.id = dra.document_request_id
INNER JOIN hospitals h on dr.hospital_id = h.id
GROUP BY h.name

Regards,

Patrick

healthcheckinc

ASKER

That just gives me a 1 in the column next to the hospital. I need a count of all the account_ids in there but do not need the account duplicates. Heres an example

acctID hospital
123 ABC hospital
123 ABC hospital
234 ABC Hospital
567 ABC hospital

I would need the result set to look like the following
AcctIDCount Hospital
3 ABC Hospital

123 would only count as 1

Hope this helps

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

healthcheckinc

ASKER

I still am not sure this is working correctly. Could there possibly be another way to write this?

Raja Jegan R

Can you try this one out:

Select COUNT(acctID) as AcctIDCount, h.name
from document_requests dr
INNER JOIN document_request_accounts dra on dr.id = dra.document_request_id
INNER JOIN hospitals h on dr.hospital_id = h.id
GROUP BY h.name

Hope this helps

Anthony Perkins

>>I would need the result set to look like the following
AcctIDCount Hospital
3 ABC Hospital
<<
So what happened to 567 ABC Hospital?

rrjegan17,

Not sure if you realize but the solution you posted, except for the alias, is identical to the query in the original question. COUNT(*) = COUNT(acctID)

tculler

Alright, first of all, you're grouping by the wrong field. What a GROUP BY clause does is merge ALL matching fields into a single record that share that same field value. However, what exactly do you want to count? Just the total number of entries in the tables, the individual numbers of duplicates of each AccountID or Name, what? I can get you started, but I need more information. Try to be more specific in your question--what EXACTLY do you want to see in your query? How many fields? What should the fields be? What is the schema of the relevant tables?

Instead of grouping by h.name, group by account ID; in other words, ... GROUP BY tableName.AccountID, sustituting names for your names.

Anthony Perkins

Actually on second thoughts matthewspatrick has given you the solution. Here is the code to prove it:

Declare @Accounts table (
acctID varchar(10),
name varchar(50))

Insert @Accounts (acctID, name) Values ('123', 'ABC hospital')
Insert @Accounts (acctID, name) Values ('123', 'ABC hospital')
Insert @Accounts (acctID, name) Values ('234', 'ABC hospital')
Insert @Accounts (acctID, name) Values ('567', 'ABC hospital')

Select name, COUNT(DISTINCT acctID)
from @Accounts
GROUP BY name

Output:

name
-------------------------------------------------- -----------
ABC hospital 3

healthcheckinc

ASKER

After reviewing the data your post was right. I appreciate it...

tculler - Take a xanax