Home » SQL & PL/SQL » SQL & PL/SQL » How to count records from a population?
How to count records from a population? [message #240406] Thu, 24 May 2007 15:06 Go to next message
asparrow
Messages: 8
Registered: May 2007
Junior Member
Embarassed

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 #240409 is a reply to message #240406] Thu, 24 May 2007 15:08 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
post your
desc test.table
Re: How to count records from a population? [message #240412 is a reply to message #240406] Thu, 24 May 2007 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a clear description of all your tables (column names and datatypes), an example of data, an example of the result you want.
Don't forget the Oracle version.
Also as you are new, read and apply:
How to format your posts
How to get a quick answer to your question: TIPS AND TRICKS

Regards
Michel
Re: How to count records from a population? [message #240415 is a reply to message #240409] Thu, 24 May 2007 15:25 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Trigger on a View
Next Topic: size of varchar2 in pl/sql
Goto Forum:
  


Current Time: Fri Dec 09 09:42:45 CST 2016

Total time taken to generate the page: 0.08829 seconds