Having a subquery somehow do the same group by as the outer query

2 votes

1 answer

2256 views

                          i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance.

Basically we're doing an over all count and grouping these counts by days... but we'd also like to kind of subquery those counts to get a specific type of count.

This is what I have so far that's not quite right.

    select d.start_date, 
           count(a.status),
           (   select count(checked_ins) as checked_ins 
               from (   select distinct(a.id) as checked_ins 
                        from schema1.employee_se_assignment a, 
                             schema2.se d
                        where a.se_id= d.id  
                          and a.status not in ('unassigned')
                          and d.customer_name in (‘Test Client’)
                          and a.status = 'checked_in'
                    ) src
           )
    from schema1.employee_se_assignment a, 
         schema2.se d
    where a.se_id= d.id
      and a.status not in ('unassigned') 
      and d.customer_name in (‘Test Client’)
      and a.status = 'checked_in'
    group by d.start_date
    Order by d.start_date

that yields the following results. which is almost what he wants. but it's clearly yielding the total of all of a certain type of status when we want it broken down by day. EG (see below): 


                        

Asked by die Wolken (121 rep)

May 20, 2019, 09:47 AM
Last activity: Aug 4, 2025, 07:05 PM

Having a subquery somehow do the same group by as the outer query

Related Questions