Multiple count() from a single column

I have a table, Message, which has an FK relationshp to the table UserCDV (o
n
MessageID) and also an FK relationship to the table Document (on DocumentID)
.
Doing a join on these tables associates a message (from Message) with a
particular user (from UserCDV) and what type of document the message
involves--radiology, transcription, lab report, etc. (from the
DocumentCategoryID field of Document).
What I need to do is return, in one record, a user's ID and the count of his
messages of each of four document categories. Say for example that user
100459 has four messages with a DocumentCategoryID of 2 (Radiology), two
messages with a DocumentCategoryID of 3 (Lab), four messages with a
DocumentCategoryID of 4 (Transcription), and none with a DocumentCategoryID
of 5 (Pathology). What I need to return is a record with fields named
UserID, Rad, Lab, Tran, and Path, having the values 100459, 4, 2, 4, 0
respectively.
How can I construct a query that will select and return this information?Fred Sawtelle wrote:
> I have a table, Message, which has an FK relationshp to the table UserCDV
(on
> MessageID) and also an FK relationship to the table Document (on DocumentI
D).
> Doing a join on these tables associates a message (from Message) with a
> particular user (from UserCDV) and what type of document the message
> involves--radiology, transcription, lab report, etc. (from the
> DocumentCategoryID field of Document).
> What I need to do is return, in one record, a user's ID and the count of h
is
> messages of each of four document categories. Say for example that user
> 100459 has four messages with a DocumentCategoryID of 2 (Radiology), two
> messages with a DocumentCategoryID of 3 (Lab), four messages with a
> DocumentCategoryID of 4 (Transcription), and none with a DocumentCategoryI
D
> of 5 (Pathology). What I need to return is a record with fields named
> UserID, Rad, Lab, Tran, and Path, having the values 100459, 4, 2, 4, 0
> respectively.
You will need to do a COUNT(*) and use a group by to aggregate the count
on the user and documenttype fields. Below is an pseudo query that shows
a way to accomplish this.
SELECT
UserID,
DocumentType,
COUNT(*)
FROM users
join messages on ...
join documents on ...
GROUP BY
UserId,
DocumentType
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Fred Sawtelle wrote:
> I have a table, Message, which has an FK relationshp to the table UserCDV
(on
> MessageID) and also an FK relationship to the table Document (on DocumentI
D).
> Doing a join on these tables associates a message (from Message) with a
> particular user (from UserCDV) and what type of document the message
> involves--radiology, transcription, lab report, etc. (from the
> DocumentCategoryID field of Document).
> What I need to do is return, in one record, a user's ID and the count of h
is
> messages of each of four document categories. Say for example that user
> 100459 has four messages with a DocumentCategoryID of 2 (Radiology), two
> messages with a DocumentCategoryID of 3 (Lab), four messages with a
> DocumentCategoryID of 4 (Transcription), and none with a DocumentCategoryI
D
> of 5 (Pathology). What I need to return is a record with fields named
> UserID, Rad, Lab, Tran, and Path, having the values 100459, 4, 2, 4, 0
> respectively.
You will need to do a COUNT(*) and use a group by to aggregate the count
on the user and documenttype fields. Below is an pseudo query that shows
a way to accomplish this.
SELECT
UserID,
DocumentType,
COUNT(*)
FROM users
join messages on ...
join documents on ...
GROUP BY
UserId,
DocumentType
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Thanks for your answer, Aaron. That query segregates the data by
DocumentCategoryID, but it returns a separate record for each distinct
DocumentCategoryID. I need to get all four totals in a single record.
Here's how I fleshed out the pseudoquery:
SELECT
UserID,
DocumentCategoryID,
COUNT(*) as Count
FROM UserCDV u
join Message m on u.MessageID = m.MessageID
join Document d on m.DocumentID = d.DocumentID
WHERE UserID = 100459
AND DocumentCategoryID in (2, 3, 4, 5)
GROUP BY
UserID,
DocumentCategoryID
...and here's the result - three records.
UserID DocumentCategoryID Count
-- -- --
100459 2 784
100459 3 7685
100459 4 1449|||Fred Sawtelle wrote:
> Thanks for your answer, Aaron. That query segregates the data by
> DocumentCategoryID, but it returns a separate record for each distinct
> DocumentCategoryID. I need to get all four totals in a single record.
I wish we had Pivot already... (enough day dreaming).
Check out this page for some code examples.
http://www.aspfaq.com/show.asp?id=2462
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||Excellent! With the guidance of those examples i now have it working.
Thanks so much.

> http://www.aspfaq.com/show.asp?id=2462

Multi-part Identfier could not be found (with SQL Script)

Hi all,

I have attached the following script from which the error message come from.

Please help me out.

use PatientCare

go

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

Error message got is as below:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "pr.PatientId" could not be bound.

Thanx in advance.

Ronald

Ronaldlee Ejalu wrote:

use PatientCare

go

select pr.PatientId, p.FirstName,p.LastName, m.Name

from Prescription as pr inner join Patient as p inner join Medicine as m

on p.PatientId = pr.PatientId

on pr.MedicineCode = m.MedicineCode;

SELECT pr.PatientID, p.FirstName, p.LastName, m.Name

FROM Prescription pr

JOIN Patient p ON p.PatientID = pr.PatientID

JOIN Medicine m ON pr.MedicineCode = m.MedicineCode

or you can do it this way

SELECT pr.PatientID, p.FirstName, p.LastName, mName

FROM Prescription pr, Patient p, Medicine m

WHERE p.PatientID = pr.PatientID AND pr.MedicineCode = m.MedicineCode

Adamus

|||

hi ronald

looks like you got a syntax error and adamus is right

i deleted you other post cause it looks identical to this one

ragards

|||

Just to add what to has been said. You have nested joins, which I have bolded here:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
inner join Medicine as m
on p.PatientId = pr.PatientId
on pr.MedicineCode = m.MedicineCode;

This nested join has to operate first, then the next join:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join PatientMedicineJoinResults
on pr.MedicineCode = m.MedicineCode;

The point of this is to simplify how queries can be written when you are joining together tables, especially for lookup tables where there are outer joins involved:

select <columnlist>
from table
left outer join table2
inner join table2Lookup
on table2.table2LookupId = table2Lookup.table2LookupId
on table.tableKey = table2.tableKey
left outer join table3
inner join table3Lookup
on table3.table3LookupId = table3Lookup.table3LookupId

Not, of course that any of this matches what you need :) In your case, you are joining each of the tables to the prescription table, so the join criteria must match that, and not be nested:

select pr.PatientId, p.FirstName,p.LastName, m.Name
from Prescription as pr
inner join Patient as p
on p.PatientId = pr.PatientId
inner join Medicine as m
on pr.MedicineCode = m.MedicineCode;

|||

Hi all,

I really thank you for this.

It worked.