Select statement with Count within another Select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement with Count within another Select

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2013-08-23 : 11:55:48

I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).

Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. I can't figure out how to count in the sunquery and have the result placed within the groups results in the main query.

SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2

FROM event_summary es, event_detail ed
where es.event_id = ed.event_id
AND es.fy = '&FY'
GROUP BY es.Event_Name, es.event_id
ORDER by es.Event_Name

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-23 : 14:30:34

When you add the donor_id count, the way you have structured it, it seems like you want one row for each event_name, event_id, and id_number. Is that the case, or are you just looking to count the number of donors for each event_name and event_id combination? You can try the query below, but I am not sure if it will be correct because of double-counting issues. If you can post some sample data and DDLs for the table sthat would help.

SELECT  es.event_name ,
        es.event_id ,
        COUNT(ed.id_number) Number_Attendees ,
        SUM(count2) AS DonorCount
FROM    event_summary es ,
        INNER JOIN event_detail ed
		ON   es.event_id = ed.event_id
        AND es.fy = '&FY'
        OUTER APPLY
        (	SELECT COUNT(gifts.donor_id) AS Count2
			  FROM      gifts
			  WHERE     gifts.donor_id = ed.id_number
		)s
GROUP BY es.Event_Name ,
        es.event_id
ORDER BY es.Event_Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources