SQL Server 2012 – Count Number of Occurrences in Query Result

sql serversql-server-2012

I am running the below query on my sql server 2012 database.

select Appointment.AppointmentDate, Appointment.AppointmentTime,   Auctioneer.AuctioneerName, Auctioneer.AuctioneerSurname, Buyer.BuyerName,   Buyer.BuyerSurname  
from Appointment  
inner join Auctioneer on Appointment.AuctioneerId=Auctioneer.AuctioneerId  
inner join Buyer on Appointment.BuyerId=Buyer.BuyerId  
where AppointmentDate between '2017-01-01' and '2017-12-31'

The Result of this is the below:

+-----------------+-----------------+----------------+-------------------+-----------+----------------+
| AppointmentDate | AppointmentTime | AuctioneerName | AuctioneerSurname | BuyerName | BuyerSurname   |
+-----------------+-----------------+----------------+-------------------+-----------+----------------+
| 2017-10-23      | 13:00:00        | Mary           | Borg              | David     | Borg           |
| 2017-10-24      | 15:30:00        | Mary           | Borg              | Joseph    | Sammut         |
| 2017-11-03      | 09:30:00        | Joseph         | Smith             | Mark      | Psaila         |
| 2017-11-03      | 10:45:00        | Joseph         | Smith             | David     | Borg           |
| 2017-11-15      | 10:10:00        | Mary           | Borg              | David     | Borg           |
| 2017-08-02      | 08:30:00        | Daisy          | Webb              | Josephine | Grima          |
| 2017-04-15      | 14:00:00        | Sam            | King              | Mary      | Santucci       |
+-----------------+-----------------+----------------+-------------------+-----------+----------------+

I need to add a column which counts the number of occurrences of the 'AuctioneerName' & AuctioneeSurname' in the result.

Example: Mary Borg Count 3, Joseph Smith Count 2 etc…

Best Answer

I think you just need a window count():

COUNT(*) OVER (PARTITION BY AuctioneerSurname, AuctioneerName)

The query with the added computation - and using aliases for the long table names:

select 
    app.AppointmentDate,  app.AppointmentTime,
    auc.AuctioneerName,  auc.AuctioneerSurname, 
    buy.BuyerName,  buy.BuyerSurname,
    AuctioneerCount = count(*) over
                         (partition by auc.AuctioneerSurname, auc.AuctioneerName)
from 
    Appointment as app
    inner join Auctioneer as auc 
        on app.AuctioneerId = auc.AuctioneerId  
    inner join Buyer as buy 
        on app.BuyerId = buy.BuyerId  
where
    app.AppointmentDate between '2017-01-01' and '2017-12-31' ;
Related Question