2

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...

2
  • 2
    Is there a reason you're wanting to group by name and surname instead of their Id?
    – Rob
    Dec 4 '17 at 1:51
  • good question never get answered,also the quetion is upvoted.group by clause or window partition function should on id.
    – KumarHarsh
    Dec 5 '17 at 8:49
9

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' ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.