CODEWITHSUNDEEP
sql-servert-sql
Beefybanana
Beefybanana

Reputation: 87

Is There a Way to Use count in a subquery

ER Diagram

As you can see in the link of the ER Diagram, I got two tables, Department and Admissions. My goal is to print out only the Reshnum of the Department that has the most Admissions.

My first attempt was this:

select top 1 count(*) as Number_of_Adm, Department.ReshNum
from Admission 
inner join Department on Admission.DepartmentId = Department.id
group by Department.ReshNum 
order by Number_of_Adm;

It's pretty straight forward, counts the rows, groups them to the department and prints out the top answer after ordering for the highest count. My problem is that it prints both the count and the Rashnum.

I'm trying to only print the Rashnum (name of the branch/serialnumber). I've read up on sub queries to try to get the count in a subquery and then pick the branch out from that query, but I can't get it to work.

Any tips?

Upvotes: 1

Views: 1145

Answers (2)

JakeMc
JakeMc

Reputation: 514

Great question! Stu's answer is probably the most optimum way, depending on your indexes.

Just for posterity, since your inquiry includes how to make a subquery work, here is an alternative using a subquery. As far as I can tell, at least on my database, SQL Query Optimizer plans the two queries out with about the same performance on either version.

Subqueries can be really useful in tons of scenarios, like when you want to add another field to display and group by without having to add every single other field on the table in the group by clause.

SELECT TOP 1 x.ReshNum /*, x.DepartmentName*/
FROM
(
    SELECT count(*) AS CountOfAdmissions, d.CustomerNumber /*d.DepartmentName*/
    FROM Adminssion a
    INNER JOIN Department d ON a.DepartmentId= d.Id
    GROUP BY d.ReshNum /*, d.DepartmentName*/
    /*HAVING count(*) > 50*/
) x
ORDER BY CountOfAdmissions DESC

How it works:

You need to wrap your subquery in parenthesis and give that subquery an alias. Above, I have it aliased as x just outside the closing parenthesis as an arbitrary identifier. You could certainly alias it as depts or mySubQuery or whatever reads well in the resulting overall query to you.

Next, it's important to notice that while the group by clause can be included inside the subquery, the order by clause cannot. So you have to keep the order by clause on the outside of the query, which means you are actually ordering the results of the subquery, and not the results of the actual table. Which could be great for performance because the result of your subquery is likely to be vastly smaller than the whole table. However, it will not use your table's index that way, so depending on how your indexes are, that bonus may wash out or even be worse than ordering without a subquery.

Last, one of the benefits of this kind of subquery approach is that you could easily throw in another field if you want, like the department name for example, without costing very much in performance. Above I have that hypothetical department name field commented out between the /* and */ flags. Note that it is referenced with the d table alias on the inside of the subquery, but uses the subquery's x alias outside of the subquery.

Just as a bonus, in case it comes up, also commented out above is a having clause that you might be able to use. Just to show what could be done inside the subquery.

Cheers

Upvotes: 0

Stu
Stu

Reputation: 32579

You just need to select the column you need and move the count to the order by criteria.

Using column aliases also helps make your query easier to follow, especially with more columns & tables in the query.

you also say you want the most, I assume you'll need to order descending.

select top (1) d.ReshNum
from Admission a
inner join Department d
on a.DepartmentId = d.id
group by d.ReshNum
Order By count(*) desc;

Upvotes: 2

Related Questions