May 20, 2009 at 4:20 am
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
May 20, 2009 at 4:48 am
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
May 20, 2009 at 4:57 am
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
May 20, 2009 at 5:28 am
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
May 20, 2009 at 6:14 am
Hi justanewone
Nice trick! Never saw a PARTITION BY NULL!
May 20, 2009 at 6:18 am
I have seen first time PARTITION BY NULL
good one............
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply