|
| |
|
|
patricksims
Posts: 45
Joined: 7/7/2005
Status: offline
 |
SQL select count column with multiple values - 8/19/2005 15:00:58
I am not sure how to word this other than describing what I am trying to accomplish. I have a db field (call it fdisp) that collects different values 01-10 (each number corresponding with some meaning). Each record would also have a field called agent. I want to be able to do a count of each possible value, grouped by agent. So if one of the agents is john and he submitted 5 records each having one of the possible fdsip values, I want it to return like:
agent count_of_01 count_of_02 count_of_03
john 2 1 2
Is this possible? I was thinking something like: count([fdisp]='01'),count([fdisp]='02)...but I don't think that syntax will fly.
|
|
|
|
BeTheBall
Posts: 6502
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL select count column with multiple values - 8/19/2005 18:09:15
I can get you the results you want, but not the format. The SQL would be along the lines of:
SELECT DISTINCT fdisp, agent, Count(*) as theCount FROM tableName GROUP BY fdisp, agent
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
sekadmin
Posts: 1
Joined: 6/1/2006
Status: offline
|
RE: SQL select count column with multiple values - 6/1/2006 19:49:37
I have an almost identical issue, but enough different that I am not getting the results I am after. In my case I am wanting to find out how many documents a user has in 5 possible states (new, pending, overdue, completed, and trashed). I really only want to know how many documents are in the first 3 states. The issue I have with the sql statement given is that it does not provide a result of 0 if there are no documents in a particular state. Is this possible? My ideal output would be in a table format with the following column headings:
staff ID #new #pending #overdue
Possible????
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 