Simplify a query with multiple aggregate subquerie

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

SQL Server 2008 Forums

Transact-SQL (2008)

Simplify a query with multiple aggregate subquerie

Author

Topic

pixelwiz
Starting Member

25 Posts

Posted - 2011-02-14 : 13:49:14

Hi All,

Does anyone know if there is a way to simplify this query? (Hopefully the names are obvious enough, it has to do with golf, courses, and 18 holes, but I need to do total par for front 9 and back 9. I think there is a way to do it using a having clause, but I'm not sure.


SELECT c.*, ec.course_priority, (	
	SELECT SUM(yardage) 
	FROM holes h
	WHERE h.event_id = ec.event_id
	AND h.course_id = c.id) yardage,(	
	SELECT SUM(par) 
	FROM holes h
	WHERE h.event_id = ec.event_id
	AND h.course_id = c.id
	AND h.hole_number <= 9) front,(	
	SELECT SUM(par) 
	FROM holes h
	WHERE h.event_id = ec.event_id
	AND h.course_id = c.id
	AND h.hole_number > 9) back, (	
	SELECT SUM(par) 
	FROM holes h
	WHERE h.event_id = ec.event_id
	AND h.course_id = c.id) total
      FROM courses c
      INNER JOIN event_courses ec
      ON ec.course_id = c.id
      AND ec.event_id = 5001
	
				WHERE c.id IN 
				(
					SELECT DISTINCT course_id
					FROM groups g
					WHERE g.round = 1
					AND g.event_id = ec.event_id
				) 
			ORDER BY ec.course_priority

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 14:35:38

This is a guess as you didn't provide any DDL, how the tables join or any sample data, but it's a step in the right direction.

Jim

SELECT c.*,ec.course_priority
     ,[FrontNine] = SUM(CASE WHEN h.holeNumber  < 10 THEN par ELSE 0 END)
     ,[BackNine]  = SUM(CASE WHEN h.holeNumber   > 9 THEN par ELSE 0 END)
     ,[Total]     = SUM(par) 

FROM holes h
INNER JOIN courses c on 
                           h.course_id = c.id
                       and h.
INNER JOIN event_courses ec on
                           h.event_id = ec.event_id
                       and c.id = ec.course_id
WHERE ec.event_id = 5001
GROUP BY <the columns in courses>,ec.course_priority

Everyday I learn something that somebody else already knew