qury

Question

Post reply

qury

mayank jarsaniya

Hall of Fame

Points: 3532
More actions
May 20, 2009 at 4:20 am

#209681

create table temp(id int,name varchar(50))

insert into temp

select 1,'m' UNION ALL

select 2,'ma' UNION ALL

select 3,'may' UNION ALL

select 4,'ma' UNION ALL

select 5,'m' UNION ALL

i need select query like

select distinct(name),count(name),count(*) as total row

count(*) as total row = total rows in my table ie 5 in this example

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

arun.sas

SSChampion

Points: 11831

May 20, 2009 at 4:48 am

#997491

Hi,

try this

1)

select name, count(name)row,

(select count(name)from #temp)as total_row

from #temp

group by name

2)

select a.name, count(a.name)row,

b.total_row

from #temp as a,(select count(name)as total_row from #temp)as b

group by a.name,b.total_row

ARUN SAS

mayank jarsaniya Hall of Fame Points: 3532 More actions · Answer 2

it works but i am triying to eliminate two table scan of same table...

ie. exact solution is

select name,count(name),(select count(*) from temp)

from temp

group by name;

but this one takes two table scan of same table temp

i am trying to finish in one scan........

Although i don't know it is possible or not .

All suggestions are welcomed

justanewone Ten Centuries Points: 1242 More actions · Answer 3

justanewone

Ten Centuries

Points: 1242

May 20, 2009 at 5:28 am

#997499

I would personally use the Partition By Null as follows if you want just an overall total.



SELECT DISTINCT(A.Name), Count(A.Name), A.Total

FROM

(

SELECT Name, Count(*) OVER (PARTITION BY Null) AS Total FROM Temp

) AS A

GROUP BY A.Name, A.Total