1

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

 g

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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.

  • thank you for your reply. however. we're not just counting all statuses twice, it's the first overall total and the 2nd where status is equal to checked_in as the where query states – Mag Musik May 21 at 9:23
  • @MagMusik, sorry, I don't understand your comment. Both your subselect and your main select contain status = 'checked_in', and my proposed query does as well. Where is the difference? – jjanes May 21 at 13:26
  • your query doesn't yield the results we want. i'm not looking to count status and distinct ids as total where all are status checked_in... i want count of all statuses and then count of all statuses that are value checked_in grouped by date. – Mag Musik May 24 at 12:16
  • It seems your example query has little to do with what you want, then. Please fix your example so that it gets at least slightly close to what you want. Then we can help you get the rest of the way there. – jjanes May 24 at 14:40
  • just because you misunderstand doesn't mean it's wrong. it's crystal clear to me. – Mag Musik May 26 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.