quote:

---

Originally posted by shaggy

select "all_column_name" from GlobalDBAServer.dbo.FileGroups where SELECT [server], [database], COUNT(*)
FROM dbo.FileGroups
group by "all_column_name"
having count(*) > 2

---

quote:

---

Originally posted by GRAYWOLF

I am trying to pull up the whole table based on count(). I can do it where it pulls up what I am counting, but how do I get the rest of the info to show?

This will return the counted rows:
SELECT [server], [database], COUNT(*)
FROM dbo.FileGroups
group by [server], [database] having count(*) >2

How can I do it so that it shows every column? This gives an error "Incorrect syntax near the keyword 'SELECT'."

select * from GlobalDBAServer.dbo.FileGroups where SELECT [server], [database], COUNT(*)
FROM dbo.FileGroups
group by [server], [database] having count(*) > 2

Putting the second SELECT in () causes an error at ")".


really, I only want 2 more columns (logical & physical) but I don't want them as part of the count

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

---

SELECT f.* FROM GlobalDBAServer.dbo.FileGroups as f inner join 
(	
	SELECT [server], [database], COUNT(*) 
	FROM dbo.FileGroups 
	group by [server], [database] having count(*) > 2
) as t on f.[server]=t.[server] and f.[database]=t.[database]

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by GRAYWOLF

I am trying to pull up the whole table based on count(). I can do it where it pulls up what I am counting, but how do I get the rest of the info to show?

This will return the counted rows:
SELECT [server], [database], COUNT(*)
FROM dbo.FileGroups
group by [server], [database] having count(*) >2

How can I do it so that it shows every column? This gives an error "Incorrect syntax near the keyword 'SELECT'."

select * from GlobalDBAServer.dbo.FileGroups where SELECT [server], [database], COUNT(*)
FROM dbo.FileGroups
group by [server], [database] having count(*) > 2

Putting the second SELECT in () causes an error at ")".


really, I only want 2 more columns (logical & physical) but I don't want them as part of the count

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

---

Try

SELECT f.*,t.DBCount FROM GlobalDBAServer.dbo.FileGroups as f inner join 
(	
	SELECT [server], [database], COUNT(*) AS DBCount
	FROM dbo.FileGroups 
	group by [server], [database] having count(*) > 2
) as t on f.[server]=t.[server] and f.[database]=t.[database]

Madhivanan

Failing to plan is Planning to fail

---

SELECT *,COUNT(1) OVER (PARTITION BY  [server], [database]) AS DBCount 
FROM GlobalDBAServer.dbo.FileGroups