CODEWITHSUNDEEP
mysqlcountdistinct
user2415706
user2415706

Reputation: 982

SQL return distinct values and their combined count in one query

Using MySQL and am supposed to return the number of absentees and their names. I do a query for all students and subtract the subquery of ones who showed up that day.

Is this even possible to do in 1 query? I know how to do it with 2 separate ones and they return statements of different sizes so I'm not sure these are compatible.

For list of names:

select distinct name 
from submissions join hacker
on submissions.hacker_id=hacker.hacker_id
where name not in
(
  select distinct name 
from submissions join hacker
on submissions.hacker_id=hacker.hacker_id
where sub_date = '2019-12-07'  
  )

For count:

select count(name) 
from submissions join hacker
on submissions.hacker_id=hacker.hacker_id
where name not in
(
  select distinct name 
from submissions join hacker
on submissions.hacker_id=hacker.hacker_id
where sub_date = '2019-12-07'  
  )

Upvotes: 0

Views: 41

Answers (1)

Barmar
Barmar

Reputation: 781726

You can use a CTE to perform the query once, then use UNION to combine the count and names into a single result.

WITH absentees as (select distinct name 
    from submissions join hacker
    on submissions.hacker_id=hacker.hacker_id
    where name not in
    (
      select distinct name 
    from submissions join hacker
    on submissions.hacker_id=hacker.hacker_id
    where sub_date = '2019-12-07'  
    )
)
SELECT * FROM absentees
UNION ALL
SELECT COUNT(*) FROM absentees

This will put the total as the last line in the result.

Upvotes: 1

Related Questions