Group by issue

Question

Post reply

Group by issue

Raja mssql

Mr or Mrs. 500

Points: 565

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 2 months, 1 week ago by Raja mssql.
This topic was modified 2 months, 1 week ago by Raja mssql.
This topic was modified 2 months, 1 week ago by Raja mssql.
This topic was modified 2 months, 1 week ago by Raja mssql.
This topic was modified 2 months, 1 week ago by Raja mssql.
This topic was modified 2 months, 1 week ago by Raja mssql.

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: 398329 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
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software

drew.allen SSC Guru Points: 76867 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

Raja mssql Mr or Mrs. 500 Points: 565 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: 17966 More actions · Answer 4

Raja mssql wrote:

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

Did you terminate the previous statement with a semicolon?

Raja mssql Mr or Mrs. 500 Points: 565 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 2 months, 1 week ago by Raja mssql.
This reply was modified 2 months, 1 week ago by Raja mssql.

Raja mssql Mr or Mrs. 500 Points: 565 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