Subquery within Select Statement
<p>I've written a query that returns a count of open support tickets for a group of agents. The query currently returns tickets that have been older than 5 days:</p>

<pre><code>SELECT groupname, 
       assignee, 
       Count(*) AS [Tickets Older Than 5 Days] 
FROM   zendeskunclosedticketexport 
WHERE  ( groupname = 'DC Web Ops' 
          OR groupname = 'DC Maintenance' 
          OR groupname = 'DC QC1' ) 
       AND ticketcreateddate &lt; Dateadd(day, -5, Getdate()) 
GROUP  BY groupname, 
          assignee 
ORDER  BY groupname, 
          Count(*) DESC
</code></pre>

<p>Query result sample:</p>

<pre><code>groupname       assignee                Tickets Older Than 5 Days

DC Maintenance  Mubarak Ali             12
DC Maintenance  Abirami Selvanathan     11
DC Maintenance  Jemima Mohanraj         6
DC Maintenance  Praveena Rajan          6
DC Maintenance  Rahul Ramesh            6
DC Maintenance  Saravanan Varadharajan  6
DC Web Ops  Kasey Barber                16
DC Web Ops  Jamie Raines                11
DC Web Ops  Keith Washington            10
DC Web Ops  Patricia Gomez Imvendorize  6
</code></pre>

<p>What I have been trying to do is also include columns for 10 days, 30 days, etc. but have been unable to do so. I have attempted to write a subquery within the select statement to return a count for the respective # of days; something like the below:</p>

<pre><code>SELECT groupname, 
       assignee [currentAssignee],


       (SELECT Count(*) AS [Tickets Older Than 5 Days] 
            FROM   zendeskunclosedticketexport 
            WHERE  ( 
                    ticketcreateddate &lt; Dateadd(day, -5, Getdate())
                    AND
                    assignee = [currentAssignee]
        )

       (SELECT Count(*) AS [Tickets Older Than 10 Days] 
            FROM   zendeskunclosedticketexport 
            WHERE  ( 
                    ticketcreateddate &lt; Dateadd(day, -10, Getdate())
                    AND
                    assignee = [currentAssignee]
        )

        (SELECT Count(*) AS [Tickets Older Than 30 Days] 
            FROM   zendeskunclosedticketexport 
            WHERE  ( 
                    ticketcreateddate &lt; Dateadd(day, -30, Getdate())
                    AND
                    assignee = [currentAssignee]
        )
FROM   zendeskunclosedticketexport
</code></pre>

<p>What I am essentially trying to do here is pass the Assignee value from the outer query to the inner queries...I know that as I have it this isn't legal, but hopefully it provides some context as to the approach I'm taking. Is there any way to pass data from an outer query to an inner query in the form of a temporary variable?</p>

<p>EDIT: User notified me that I was missing a FROM clause, have since added this.</p>