S.BM S.BM - 5 months ago 10
SQL Question

Calculate Count from a view ( SQL)

I have this View :

SELECT [ID]
,[PersonName]
,[PersonFUNCTION]
,[GUESTName]
,[Team]
,[sector]
,[MeetingCity]
,[GUESTCOMPANY]
,[TypeMeeting]
FROM [DB_TEST].[dbo].[Meetings]


From this view we can Read for Exemple :

The Person with the Name "XXX" (PersonName) who is A CEO (PersonFUNCTION) was in a meeting with "Mark Zuckerberg" (GUESTName) in Paris ( MeetingCity ) and "Facebook" is the ( GUESTCOMPANY ) and finally the meeting was a "One to One Meeting" (TypeMeeting) !

PS : Note that XXX can meet Mark Zuckerberg more than one time , in a different city for example.

What I Want to do is :

Add 3 columns : Count( One to One Meeting ) and Count( One to Few Meeting ) and Count ( Group Meeting )


  • Count( One to One Meeting ) = how many times the [PersonName] has met the [GUESTName] in a One to One meeting no matter if the city is different or anything else is different ...



So something like that :

SELECT [ID]
,[PersonName]
,[PersonFUNCTION]

,Count( One to One Meeting between PersonName and GUESTName ) ?
,Count( One to Few Meeting between PersonName and GUESTName) ?
,Count ( Group Meeting between PersonName and GUESTName) ?

,[GUESTName]
,[Team]
,[sector]
,[MeetingCity]
,[GUESTCOMPANY]
,[TypeMeeting]
FROM [DB_TEST].[dbo].[Meetings]


Thanks

Answer

BM

Another solution might be using SQL Pivot query

Here is sample data and SQL pivot Select statement

SELECT *
FROM (
  SELECT
    [ID],
    [PersonName],
    [TypeMeeting]
  FROM [Meetings]
) TableData
PIVOT (
  Count(ID)
  FOR [TypeMeeting] IN (
    [T1],[T2],[T3],[T4],[T5],[T6]
  )
) PivotTable

I just used 'T1', etc for meeting types, you are required to replace them with actual values within "[]"

Here is the result

enter image description here

If you have many different meeting types you can use dynamic pivot query in SQL Server but I guess above solution will be enough

Comments