Nested SQL Count

Posted on 2012-09-10
Last Modified: 2012-09-10
I'm sure this should be a fairly simple query, but can't figure it out...

I have three tables that I would like to choose information from and present in a single query, and their setup is as follows:

Table: EventDestination
ID: (PK) non null int
Name: nvarchar non null

Table: Event
ID: (PK) non null int
Date: datetime
EventDestinationID: FK to the ID of EventDestination

Table: EventAttendance
ID: (PK) non null int
EventID: FK to the ID of Event
Attendee: nvarchar

EventDestination is a simple table with the names of locations that events will be held.
The Event table just ties a destination together with a date that the event will occur on
EventAttendance simply links an event with the name of someone attending.

I can easily query a list of events and their destinations, and all I want to do is see a count of the number of people attending each event.
If I join the tables as they are at the moment (have deliberately only put two events in with 4 people each), I get 8 rows, which is what i expected - how can I put together a query to show a count of 4 next to the event, rather than all the names? Count(EventAttendance.Attendee) doesn't show what i'm after, and i'm sure it's a simple thing to do but can't figure it out.
Question by:vixtro
    LVL 25

    Accepted Solution

    Did you try?
    SELECT ed.Name, ev.Date, count(*)
    FROM EventDestination ed
    INNER JOIN Event ev ON ed.ID = ev.EventDestinationID
    INNER JOIN EventAttendance ea ON ev.ID = ea.EventID
    GROUP BY ed.Name, ev.Date

    Open in new window

    Provide an example of you expected output please.
    LVL 59

    Expert Comment

    by:Kevin Cross
    Try using a derived table to aggregate the attendee count.

    For example:
    SELECT ..., a.Num_Of_Attendees
    FROM e
    INNER JOIN d ON e.dID = d.ID
        SELECT a.eID
             , Num_Of_Attendees = COUNT(a.ID)
        FROM a
        GROUP BY a.eID
    ) a ON a.eID = e.ID

    Open in new window

    Try using the template to fill in your tables. If you have questions, please post back. I figure this way, you can learn it a little better as you apply your own table and column names.
    LVL 1

    Author Comment

    Excellent, thanks lwadwell. I think my issue was omitting one of the selected columns in the groupby statement.. Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now