May 30, 2021 at 1:30 pm
Hi ! i got a task where i am to post only the Reshnum of the Department with the highest count of admissions. there are two tables relevant to this quary. Admission and Department as you can see in the picture. where Department.ID and Admission.DepartmentID is the relation. (the reshnum is like serialnumber/name of the department).
Im having trouble quering this, my first attempt was this code:
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;
which gives a the count of the departments and prints out the department with the highest count. But i do not want the Count to be in the result(only reshnum). I tried to read up on sub queries but i cant make it work. Any tips ?
The Img attached is the part of the ER Model of the two tables.
May 30, 2021 at 5:49 pm
Your "first attempt" looks remarkably similar to the code I posted on an almost identical q of yours:
https://www.sqlservercentral.com/forums/topic/printing-the-highest-count#post-3889964
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 1, 2021 at 4:06 pm
In the future, would you mind following forum rules of been in place for over 30 years and post DDL? It would also help if you would read just one book on basic data modeling. This way you would know there is no such thing as the magic Kabbalah number "id"; RDBMS is based on logic and follows the law of identity (to be is to be something in particular; to be nothing in particular or anything in general, is to be nothing at all).
I will not open your pretty pictures. I hope you were also not in the habit of opening things from people you don't know. And not trusting most of the things you get from people you do know 🙂 Based on your narrative, I will make a guess that what you want is something like this:
CREATE TABLE Admissions
(department_id CHAR(10) NOT NULL
REFERENCES Departments
ON UPDATE CASCADE,
PRIMARY KEY(??), -- required by definition!
...);
CREATE TABLE Departments
(department_id CHAR(10) NOT NULL PRIMARY KEY,
resh_num CHAR(10) NOT NULL,
..):
Of course, I had to guess the data types, and I had to add primary keys. I hope you know that by definition, a table must have a key. This is not an option. Your rather confusing narrative makes it sound like the resh_num is the key for departments. But then why would you create a department_id? Even before we had RDBMS, the goal of all databases was to reduce redundancy, not increase it. Please notice the use of the references constraint between the two tables.
SELECT X.resh_num, MAX(X.admission_something_cnt)
FROM (SELECT D.resh_num, D.COUNT(A.admission_something) AS admission_something_cnt
FROM Admissions AS A, Departments AS D
WHERE A.department_id = D.department_id
GROUP BY D.resh_num) AS X (resh_num, admission_something_cnt)
GROUP BY X.resh_num;
Please post DDL and follow ANSI/ISO standards when asking for help.
June 2, 2021 at 1:02 am
Hi ! i got a task where i am to post only the Reshnum of the Department with the highest count of admissions. there are two tables relevant to this quary. Admission and Department as you can see in the picture. where Department.ID and Admission.DepartmentID is the relation. (the reshnum is like serialnumber/name of the department).
Im having trouble quering this, my first attempt was this code:
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;
which gives a the count of the departments and prints out the department with the highest count. But i do not want the Count to be in the result(only reshnum). I tried to read up on sub queries but i cant make it work. Any tips ?
The Img attached is the part of the ER Model of the two tables.
What do you want to happen if two or more departments are all tied for first place?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2021 at 2:12 am
Never mind. I'll just assume that ties for first need to be displayed.
WITH cteCount AS
(--==== Do the math, which seriously reduces the rowcount (has blocking operator)
SELECT AdmDeptCnt = COUNT(*)
,DepartmentID
FROM dbo.Admission
GROUP BY DepartmentID
)
,cteEnumerate AS
(--==== Rank the results on a very small number of rows.
SELECT AdmRank = DENSE_RANK() OVER (ORDER BY AdmDeptCnt DESC) (has blocking operator)
,DepartmentID
FROM cteCount
)--==== Display the joined result on an even smaller number of rows.
SELECT dept.ReshNum
FROM dbo.Department dept
JOIN cteEnumerate adm
ON dept.ID = adm.DepartmentID
WHERE adm.AdmRank = 1
ORDER BY dept.ReshNum
;
Do us a favor in return. Please explain why you'd only need to know the name of the winner and not the "score" nor how the others placed.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2021 at 9:10 pm
I think you can combine the first two queries into a single query, also avoiding having to rank all the totals:
WITH cteCombined AS
(
SELECT TOP (1) WITH TIES DepartmentID
FROM dbo.Admission
GROUP BY DepartmentID
ORDER BY COUNT(*) DESC
)
SELECT dept.ReshNum
FROM dbo.Department dept
JOIN cteCombined com
ON dept.ID = com.DepartmentID
ORDER BY dept.ReshNum
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply