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

Question

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):

jjanes · Answer

It is legal, but confusing and a bad idea, that you use the same table aliases in different levels of the query.  You could likely do what you want by adding the condition and d.start_date = d2.start_date in the subquery, but of course you would have to have one of the aliases be "d2" rather than "d" to do that.

But it seems like you could get rid of the subquery altogether by doing:

select d.start_date, 
       count(a.status),
       count(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'
group by d.start_date
Order by d.start_date

Although these do differ in how NULL values of a.id are tallied, if it is possible for there to be any of these.

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

One Answer

Add your own answers!

Ask a Question