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

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

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

Source (Stackoverflow)