Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select Querry to display no of records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
siva102

New User


Joined: 28 Nov 2007
Posts: 64
Location: Chennai

PostPosted: Wed Aug 20, 2008 12:56 pm    Post subject: Select Querry to display no of records
Reply with quote

Hi all,

I am having a table which contains 13 records. Let the table name is Employ and the column is name.

My need is to display all the names along with the total count of names.

select * from employ with ur; --> Displays all the names.

Select count(*) from employ with ur ; --> total count

How to mix thease querry so that i can get the o/p ike below,


Name Count

sa 13
as
etc.....


can anybody please help me out from this prob.
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Aug 20, 2008 1:07 pm    Post subject:
Reply with quote

Select name,count(*) from employ
group by name
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 64
Location: Chennai

PostPosted: Wed Aug 20, 2008 3:44 pm    Post subject: Reply to: Select Querry to display no of records
Reply with quote

hi Srihari,

Thanks for replying.

But in the query that u have provided i am not getting the total count of number of rows instead getting how many times the name is there in the table.


For e.g - >

Let the table contains 5 values. like below,

sanjeet
sandeep
sam
sarmi
sapnaa

By running the querry i am getting

Sanjeet - 1
sandeep - 1
like wise.....

But i need my o/p to display all the 5 names along with count = 5

I suppose u got my question. If you need anything else please let me know.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Aug 20, 2008 3:58 pm    Post subject:
Reply with quote

seems to be bit strange requirement. You can try the following query

SELECT Name, (Select count(*) from employ) as Count
from employ
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 64
Location: Chennai

PostPosted: Wed Aug 20, 2008 4:23 pm    Post subject: Reply to: Select Querry to display no of records
Reply with quote

Hi,

Yah it is little bit strange only.

But this time it got corrected 50%. The o/p is coming like the following,

Code:
Name       count
sanjeet                5
sandeep               5
sam                     5
sarmi                   5
sapnaa                 5



But i need the count to be displayed once only.

Means the o/p should look like the below,
Code:

Name                count
sanjeet                   5
sandeep                   
sam                       
sarmi                       
sapnaa                     




Thanks for all your answers.

Could you please help me regarding this issue.
Back to top
View user's profile Send private message
jegan_selvan

New User


Joined: 23 Oct 2006
Posts: 24
Location: India

PostPosted: Wed Aug 20, 2008 6:16 pm    Post subject:
Reply with quote

can you try this..

select name, count(name) from employ
group by name

Regards,

Jegan S
Back to top
View user's profile Send private message
siva102

New User


Joined: 28 Nov 2007
Posts: 64
Location: Chennai

PostPosted: Wed Aug 20, 2008 6:30 pm    Post subject: Reply to: Select Querry to display no of records
Reply with quote

Hi,

Thanks for ur reply.

I have already tried that but i am not getting my required o/p.

Here the o/p is like below,
Code:

Name       count
sapnaa       1
sarmi          1
sam            1
sandeep      1
sanjeet       1


I still not able to get my answer.
Please anybody who know the ans let me know.
Back to top
View user's profile Send private message
DB2Buddha

New User


Joined: 20 Aug 2008
Posts: 2
Location: Düsseldorf

PostPosted: Thu Aug 21, 2008 1:26 pm    Post subject:
Reply with quote

Hello Siva.

Try this

SELECT MIN(Name) AS Names, CHAR(COUNT(*)) AS Amount
FROM Employ
UNION
SELECT Name as Names, CHAR(' ') AS Amount
FROM employ
WHERE Name <> (SELECT MIN(Name) FROM Employ)
ORDER BY 1
WITH UR

But be careful. I think, the performance of this query is rather bad

Ciao, DB2Buddha
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Aug 21, 2008 8:27 pm    Post subject:
Reply with quote

Hello and welcome to the forums,

Did you test this query to make sure it provides the desired result?

Please do not post untested queries (regardless of your suspicion about performance).
Back to top
View user's profile Send private message
DB2Buddha

New User


Joined: 20 Aug 2008
Posts: 2
Location: Düsseldorf

PostPosted: Fri Aug 22, 2008 1:17 pm    Post subject: Reply to: Select Querry to display no of records
Reply with quote

Hi.

I reviewed the statement. It works under the condition, that the values of the column "name" are unique. Sorry, i did not mention that. icon_redface.gif

Otherwise the statement delivers a false result. If the column "name" is not unique, even a "union all" would not deliver the desired result.

Regards, DB2Buddha
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 22, 2008 2:57 pm    Post subject: Reply to: Select Querry to display no of records
Reply with quote

siva102,

why generate an illogical, ambigous report?

Code:

Name                count
sanjeet                   5
sandeep                   
sam                       
sarmi                       
sapnaa


why not?

Code:

Name                count
sanjeet                   1
sandeep                   1
sam                       1
sarmi                     1 
sapnaa                    1
Total Names               5


Code:

Select name
     , count(*)
from employ
group by name
Union
Select 'Total Names'
     , count(*)
from employ
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Aug 23, 2008 3:16 am    Post subject: Reply to: Select Querry to display no of records
Reply with quote

Hello,

Quote:
I reviewed the statement. It works under the condition, that the values of the column "name" are unique. . . .Sorry, i did not mention that.
Not to worry (someone is often watching. . .).

Thank you for the follow up icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Display upon console bipinpeter COBOL Programming 1 Thu Jun 22, 2017 12:35 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us