Keep the aggregated column constant in SQL Server?

user_7044005 0
Viewed 53

I am trying to do count of client locations that are count > 1, which seems to work fine as indicated in the first query.

select clientID,count(clientlocations)clientcount
from db.schema.table
group by clientid
having count(clientid) > 1

clientid         clientcount

   1                 5
   2                 7
   3                 8

But in order for me to join with the revenue I need to bring in the revenueid which fragments the clientcount.

select reveneueid,clientID,
count(clientlocations)clientcount
from db.schema.table
group by revenueid,clientid
having count(clientid) > 1

revenueid    clientid     clientcount
   5000          1            4
   6144          1            1
   4651          2            3
   4785          2            1
   8745          2            3
   7869          3            5
   4963          3            3

A client can have multiple `revenueid'.

I want to have constant count of the client likewise below.

Requested:

select reveneueid,clientID,
count(clientlocations)clientcount
from db.schema.table
group by revenueid,clientid
having count(clientid) > 1

revenueid    clientid     clientcount
   5000          1           5
   6144          1           5
   4651          2           7
   4785          2           7
   8745          2           7
   7869          3           8
   4963          3           8

I am using SQL Server 2016.

sql-server sql
1 Answers

You could use windowed SUM per clientid

select reveneueid,clientID,
  SUM(count(clientlocations)) OVER(PARITION BY clientid) AS clientcount
from db.schema.table
group by revenueid,clientid
having count(clientid) > 1
user_50708790
Related