Select Querry to display no of records

siva102 · New User Joined: 28 Nov 2007 Posts: 63 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 · Posted: Wed Aug 20, 2008 1:07 pm

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

siva102 · New User Joined: 28 Nov 2007 Posts: 63 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 · Posted: Wed Aug 20, 2008 3:58 pm

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

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

siva102 · New User Joined: 28 Nov 2007 Posts: 63 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,

jegan_selvan · New User Joined: 23 Oct 2006 Posts: 24 Location: India

can you try this..

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

Regards,

Jegan S

siva102 · New User Joined: 28 Nov 2007 Posts: 63 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,

DB2Buddha · Posted: Thu Aug 21, 2008 1:26 pm

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 · Posted: Thu Aug 21, 2008 8:27 pm

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 · Posted: Fri Aug 22, 2008 1:17 pm

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.

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

siva102,

why generate an illogical, ambigous report?

dick scherrer · Posted: Sat Aug 23, 2008 3:16 am

Hello,