IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Select Querry to display no of records


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
siva102

New User


Joined: 28 Nov 2007
Posts: 63
Location: Chennai

PostPosted: Wed Aug 20, 2008 12:56 pm
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
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: 63
Location: Chennai

PostPosted: Wed Aug 20, 2008 3:44 pm
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
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: 63
Location: Chennai

PostPosted: Wed Aug 20, 2008 4:23 pm
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
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: 63
Location: Chennai

PostPosted: Wed Aug 20, 2008 6:30 pm
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
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

Moderator Emeritus


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

PostPosted: Thu Aug 21, 2008 8:27 pm
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
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: 6966
Location: porcelain throne

PostPosted: Fri Aug 22, 2008 2:57 pm
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

Moderator Emeritus


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

PostPosted: Sat Aug 23, 2008 3:16 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
Search our Forums:

Back to Top