| How to count records from a population? [message #240406] |
Thu, 24 May 2007 15:06  |
asparrow
Messages: 8
Registered: May 2007
|
Junior Member |
|
|
Hi All,
Once again I'm back to ask help on another query that is probably very simplistic.
I have a population of records derived from a query like:
select *
from test.table
where creator in (
'user1',
'user2',
'user3',
'user4')
I would like to have output that would show two things - the first being the count of records created by each user (creator) by date (say the date field is called createDate) and I would like to include the company name associated with the userid, which is stored in another table (for discussion purposes, the second table could be called ldaptest.users. Within it, there's a column called company that houses the company name, and a second column that houses the equivalent values to creator called userid)
So, how would I enhance my basic query to count the number of records by userid and return the count plus the company name?
PS: Being very new to Oracle (obviously from the nature of these questions)and very uneducated when it comes to SQL, never having received training but nonetheless being asked to pick through data for my company, what books/training classes would you all recommend for someone who has a background writing code, but no real experience with SQL?
Any help/advice you can provide would be appreciated.
|
|
|
|
|
|
|
|
| Re: How to count records from a population? [message #240415 is a reply to message #240409] |
Thu, 24 May 2007 15:25  |
asparrow
Messages: 8
Registered: May 2007
|
Junior Member |
|
|
Test.Table looks like:
creator varchar2 36 byte
createDate date
shipnum number
The data in test.table looks like:
user1 05/23/2007 123456789
user1 05/23/2007 111111111
user1 05/23/2007 222222222
user2 05/23/2007 333333333
user2 05/22/2007 444444444
user2 05/22/2007 555555555
user3 05/22/2007 666666666
user3 05/21/2007 777777777
user2 05/21/2007 888888888
user1 05/21/2007 999999999
ldaptest.users looks like:
userid varchar2 36 byte
company varchar2 60 byte
street1 varchar2 30 byte
street2 varchar2 30 byte
city varchar2 30 byte
state varchar2 2 byte
The data in ldaptest.users looks like:
user1 Test Company A 123 Test St Attn Max Richmond VA
user2 A Testing Inc 465 Test Pl Attn Ben New York NY
user3 Company B 789 Test Sq Attn Sam Antrim MI
The data I'm trying to get to would look like this:
User1 Test Company A 5/21/2007 1
User1 Test Company A 5/23/2007 3
User2 A Testing Inc 5/21/2007 1
User2 A Testing Inc 5/22/2007 2
User2 A Testing Inc 5/23/2007 1
User3 Company B 5/21/2007 1
User3 Company B 5/22/2007 1
Where the last column is a count of shipments for each user by date.
[Updated on: Thu, 24 May 2007 15:59] Report message to a moderator |
|
|
|
| Re: How to count records from a population? [message #240417 is a reply to message #240415] |
Thu, 24 May 2007 15:32 |
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
|
Senior Member |
|
|
1 select owner,count(object_type)
2 from dba_objects
3* group by owner
SQL> /
OWNER COUNT(OBJECT_TYPE)
------------------------------ ------------------
PUBLIC 2351
SYSTEM 447
SYS 6904
TSMSYS 3
QLINK 590
OUTLN 8
DBSNMP 46
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
