IBM MAINFRAME HELP & SUPPORT FORUMS

siva102 · Joined: 28 Nov 2007 Posts: 40 Location: Chennai

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.

Srihari Gonugunta · Joined: 14 Sep 2007 Posts: 184 Location: Pune

Select name,count(*) from employ
group by name

siva102 · Joined: 28 Nov 2007 Posts: 40 Location: Chennai

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.

Srihari Gonugunta · Joined: 14 Sep 2007 Posts: 184 Location: Pune

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

SELECT Name, (Select count(*) from employ) as Count
from employ

siva102 · Joined: 28 Nov 2007 Posts: 40 Location: Chennai

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.

jegan_selvan · Joined: 23 Oct 2006 Posts: 1

can you try this..

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

Regards,

Jegan S

siva102 · Joined: 28 Nov 2007 Posts: 40 Location: Chennai

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.

DB2Buddha · Joined: 20 Aug 2008 Posts: 2 Location: D�sseldorf

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

dick scherrer · Joined: 23 Nov 2006 Posts: 8733 Location: 221 B Baker St

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).

DB2Buddha · Joined: 20 Aug 2008 Posts: 2 Location: D�sseldorf

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. :oops:

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

dbzTHEdinosauer · Joined: 20 Oct 2006 Posts: 1639 Location: germany

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

dick scherrer · Joined: 23 Nov 2006 Posts: 8733 Location: 221 B Baker St

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 :)

d