Calculate Count from a view ( SQL)

Question

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

Please don't write a paragraph describing what your view results look like - show the results. — Siyual
– Siyual, Commented Jun 30, 2016 at 14:00

JamieD77 · Accepted Answer · 2016-06-30 16:11:34Z

Something like this should help you get all columns and counts.

SELECT  [ID],
        [PersonName],
        [PersonFUNCTION],
        m2.OneToOneCount, --Count( One to One Meeting between PersonName and GUESTName )
        m2.OneToFewCount, --Count( One to Few Meeting between PersonName and GUESTName)
        m2.GroupCount,    --Count ( Group Meeting between PersonName and GUESTName) 
        [GUESTName],
        [Team],
        [sector],
        [MeetingCity],
        [GUESTCOMPANY],
        [TypeMeeting]
FROM    [DB_TEST].[dbo].[Meetings] m
        CROSS APPLY (SELECT COUNT(CASE WHEN m2.[TypeMeeting] = 'OneToOne' THEN 1 END) AS OneToOneCount,
                            COUNT(CASE WHEN m2.[TypeMeeting] = 'OneToFew' THEN 1 END) AS OneToFewCount,
                            COUNT(CASE WHEN m2.[TypeMeeting] = 'Group' THEN 1 END) AS GroupCount
                     FROM   [DB_TEST].[dbo].[Meetings] m2
                     WHERE  m2.[PersonName] = m.[PersonName]
                            AND m2.[GUESTName] = m.[GUESTName]) m2

if you can't use CROSS APPLY, this is a JOIN alternative.

SELECT  [ID],
        [PersonName],
        [PersonFUNCTION],
        m2.OneToOneCount, --Count( One to One Meeting between PersonName and GUESTName )
        m2.OneToFewCount, --Count( One to Few Meeting between PersonName and GUESTName)
        m2.GroupCount,    --Count ( Group Meeting between PersonName and GUESTName) 
        [GUESTName],
        [Team],
        [sector],
        [MeetingCity],
        [GUESTCOMPANY],
        [TypeMeeting]
FROM    [DB_TEST].[dbo].[Meetings] m
        JOIN (  SELECT  [PersonName],
                        [GUESTName],
                        COUNT(CASE WHEN m2.[TypeMeeting] = 'OneToOne' THEN 1 END) AS OneToOneCount,
                        COUNT(CASE WHEN m2.[TypeMeeting] = 'OneToFew' THEN 1 END) AS OneToFewCount,
                        COUNT(CASE WHEN m2.[TypeMeeting] = 'Group' THEN 1 END) AS GroupCount
                FROM    [DB_TEST].[dbo].[Meetings] m2
                GROUP BY [PersonName],
                        [GUESTName]
         ) m2 ON m2.[PersonName] = m.[PersonName]
             AND m2.[GUESTName] = m.[GUESTName]

Eralper · Accepted Answer · 2016-06-30 14:15:03Z

0

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

answered Jun 30, 2016 at 14:15

Eralper

6,6402 gold badges23 silver badges28 bronze badges

3 Comments

S.BM Over a year ago

Thanks for your answer ! This solution gives me the count of each type of meeting ! I have unfortunately no Idea how to introduce the GUEST name in this query in order to solve the problem - Getting counts of each type of meeting between a Person and a Guest ! Honestly it is the first time that I have to work with Pivot Tables !

underscore_d Over a year ago

@S.BM To be pedantic, Pivot Table is the name of an Excel thing, which is not directly (i.e. only conceptually) related to the pivot keyword in SQL Server.

Eralper Over a year ago

@S.BM, you can add the additional column list in the SELECT list like: SELECT * FROM ( SELECT [ID], [PersonName], GUESTName, [TypeMeeting] FROM [Meetings] ) TableData PIVOT ( Count(ID) FOR [TypeMeeting] IN ( [T1],[T2],[T3],[T4],[T5],[T6] ) ) PivotTable

Gordon Linoff · Accepted Answer · 2016-06-30 14:00:51Z

0

You can use conditional aggregation:

select PersonName, GuestName,
       sum(case when TypeMetting = 'one-to-one' then 1 else 0 end) as Num_OneToOne,
       sum(case when TypeMetting = 'one-to-few' then 1 else 0 end) as Num_OneToFew,
       sum(case when TypeMetting = 'group' then 1 else 0 end) as Num_Group
from Meetings
group by PersonName, GuestName;

answered Jun 30, 2016 at 14:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

S.BM Over a year ago

The problem with this solution is that I am no longer able to display the other Columns as they are not contained in the Group by clause ! If I add them into the Group by clause the Result will be always the same and equal 1 because each row is unique !

Eralper · Accepted Answer · 2016-06-30 14:07:35Z

0

You can use SQL COUNT function with Partition By clause

Try

SELECT Distinct [ID]
      ,[PersonName]
      ,[TypeMeeting] --...
      ,COUNT([TypeMeeting]) OVER (PARTITION BY [PersonName], [TypeMeeting]) Cnt
FROM [Meetings]

It will result as follows

answered Jun 30, 2016 at 14:07

Eralper

6,6402 gold badges23 silver badges28 bronze badges

Collectives™ on Stack Overflow

Calculate Count from a view ( SQL)

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related