Group by in subquery and base query sql server

Question

I want to know if the following is possible.

I have one table of which I want to retrieve 3 columns of data:

Day, Sum of hours worked, Sum of hours worked with condition

My query is

SELECT     Day, SUM(Regular + Extra + Overtime) AS [Potential Hours],
                      (SELECT     SUM(Extra + Regular + Overtime) AS Expr1
                        FROM          dbo.TICPlus_Effort_Billable_Only
                        WHERE      (Manager NOT LIKE '%manager1%')) AS [Billed Hours]
FROM         Billable AS Billable1
GROUP BY Day

With this query I get the sum of all the data in the subquery for each row, but I would like to have the subquery that includes the constraint to be grouped by day. Is it possible to have a group by in the subquery to do this so I get the daily sum with condition on a daily basis?

Answer 1

No, you will not be able to do that as the SUB QUERY in the SELECT list will then return more that 1 Value.

You need to do it in a SUB Query in the FROM clause

Something like

 SELECT Day, 
        SUM(Regular + Extra + Overtime) AS [Potential Hours],
        SubSum AS [Billed Hours]
FROM    Billable AS Billable1 LEFT JOIN
        (
            SELECT  Day,
                    SUM(Extra + Regular + Overtime) AS SubSum
            FROM    dbo.TICPlus_Effort_Billable_Only
            WHERE   (Manager NOT LIKE '%manager1%')
            GROUP BY Day
        ) s ON  Billable1.Day = s.Day
GROUP BY    Day