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