Group by issue

Question

Post reply

Group by issue

Saran

Right there with Babe

Points: 760

March 28, 2023 at 11:36 am

#4165933

Hi All,

I have following samples.

create table tbl_svr (Server varchar(200) primary key)
insert into tbl_svr values ('Server1')
insert into tbl_svr values ('Server2')
insert into tbl_svr values ('Server3')
insert into tbl_svr values ('Server4')
insert into tbl_svr values ('Server5')

Create table tbl_dbs_list (Server varchar(200), dbname varchar(200))
insert into tbl_dbs_list values ('Server1','db1')
insert into tbl_dbs_list values ('Server1','db2')
insert into tbl_dbs_list values ('Server1','db3')
insert into tbl_dbs_list values ('Server1','db4')
insert into tbl_dbs_list values ('Server1','db5')
insert into tbl_dbs_list values ('Server2','db1')
insert into tbl_dbs_list values ('Server2','db2')
insert into tbl_dbs_list values ('Server3','db1')
insert into tbl_dbs_list values ('Server4','db2')
insert into tbl_dbs_list values ('Server5','db1')


select d.dbname [DB_name],count(*) as Count
 from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
 group by d.dbname
 having count(*)>1

-- Result should be like this:
Server db_name
Server1 db1
Server1 db2
Server2 db1
Server2 db2
Server3 db1
Server4 db2
Server5 b1

-- If I pass db name that will work, But I want to do as  having count(*)>1
select a.Server,d.dbname [DB_name]
 from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
 where d.dbname in ('db1','db2')

-- Errors out
select a.Server,d.dbname [DB_name]
 from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
 --where d.dbname in ('db1','db2','db3','db4','db5')
  having count(*)>1

In the following, How can add server name as well for the count more than 1, If add server name all are getting group by and I did not get any results.

If I add server name I am getting no rows. That's expected. Not sure, How to get server name along with it, wherever db count is more than one.

Not working:

select a.Server,d.dbname [DB_name]
from tbl_svr A join tbl_dbs_list D on (a.Server=d.Server)
--where d.dbname in ('db1','db2','db3','db4','db5')
having count(*)>1

This is working, but server name, I can not add. If i add, i do not get any data.

How to get server name and db name wherever count >1

select d.dbname [DB_name],count(dbname) as Count
 from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
 group by d.dbname
 having count(*)>1

-- No data coming.
select A.Server,d.dbname [DB_name],count(dbname) as Count
 from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
 group by d.dbname,A.Server
 having count(*)>1

Thank you.

This topic was modified 1 year, 7 months ago by Saran.
This topic was modified 1 year, 7 months ago by Saran.
This topic was modified 1 year, 7 months ago by Saran.
This topic was modified 1 year, 7 months ago by Saran.
This topic was modified 1 year, 7 months ago by Saran.
This topic was modified 1 year, 7 months ago by Saran.

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

That's because based on your basic data set, no combination of server and database has a count above one. Put a second instance of Server4 db2 in there and you'll see it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

drew.allen SSC Guru Points: 76985 More actions · Answer 2

I think you are looking for something like the following:

WITH Server_Databases AS
(
    select a.Server,d.dbname [DB_name], COUNT(d.dbname) OVER(PARTITION BY a.Server) AS cnt
      from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
     where d.dbname in ('db1','db2')
)
SELECT sd.Server, sd.dbname
FROM Server_Databases AS sd
WHERE sd.cnt > 1

The WHERE clause is evaluated before the SELECT clause in a simple query, so you need to use the CTE to force the COUNT() to be evaluated first.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Saran Right there with Babe Points: 760 More actions · Answer 3

Thanks, I am getting following error. Also, I do not want to pass any where clause.

I am thinking, following is giving me what I need, but I do not know how to join server name in this.

Select d.dbname [DB_name],count(*) as Count

from tbl_svr A join tbl_dbs_list D on (a.Server=d.Server)

group by d.dbname

having count(*)>1

Msg 156, Level 15, State 1, Line 46

Incorrect syntax near the keyword 'WITH'.

Msg 156, Level 15, State 1, Line 49

Incorrect syntax near the keyword 'OVER'.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Saran wrote:

Msg 156, Level 15, State 1, Line 46 Incorrect syntax near the keyword 'WITH'.

Did you terminate the previous statement with a semicolon?

Saran Right there with Babe Points: 760 More actions · Answer 5

Sorry, I was in legacy server. Now i have run the same in SQL 2019.

Following is the error

Msg 207, Level 16, State 1, Line 52

Invalid column name 'dbname'.

Got it - it is [DB_name]

This reply was modified 1 year, 7 months ago by Saran.
This reply was modified 1 year, 7 months ago by Saran.

Saran Right there with Babe Points: 760 More actions · Answer 6

Go the result after slight change. Thank you J. Drew Allen.

WITH Server_Databases AS
(
    select a.Server,d.dbname [DB_name], COUNT(d.dbname) OVER(PARTITION BY dbname) AS cnt
      from tbl_svr A join  tbl_dbs_list D on (a.Server=d.Server)
     --where d.dbname in ('db1','db2')
)
SELECT sd.Server, sd.db_name
FROM Server_Databases AS sd
WHERE sd.cnt > 1
order by server