[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Nested SQL Count

Posted on 2012-09-10
Medium Priority
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

lwadwell earned 400 total points
ID: 38385480
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 60

Expert Comment

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

For example:
SELECT ..., a.Num_Of_Attendees
    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.

Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 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