SQL Query - count - max

Question

I cant manage to come up with a query for a problem. I have three tables

CREATE TABLE institute (
    iid INT PRIMARY KEY,
    sign VARCHAR(127) UNIQUE, 
    city VARCHAR(127) NOT NULL,
    area INT CHECK (area>0));

CREATE TABLE desease (
    did INT PRIMARY KEY,
    name VARCHAR(127) UNIQUE,
    level INT CHECK (level>0));

CREATE TABLE studies (
    did INT,
    iid INT,
    FOREIGN KEY (did) REFERENCES desease (did),
    FOREIGN KEY (iid) REFERENCES institute (iid),
    PRIMARY KEY (iid,did));

My question is: What are the names of the deseases by the largest number of institutes from Lisbon (Lisbon beeng the city from institute). This is what i came up with but it doesnt give me the right answer.

SELECT DISTINCT D.name, MAX(I.iid)
  FROM desease D, studies S
  JOIN institute I ON (S.iid = I.iid)
 WHERE I.city = 'Lisboa' AND D.did = S.did
 GROUP BY D.nome
HAVING COUNT(I.iid) = MAX(I.city)

As an example : Imagine 5 institutes al with city = 'Lisbon' and with iid A,B,C,D,E respectevely (just for demonstration purposes, I know type is INT); 5 Diseases with name = Z,X,N,V,M respectevely.

Now lets say desease Z,X, and M are studied by institutes A,B,C (in any order), desease N is studied by D(1 inst.) and desease V is studied by E (only one). So the max number of deseases studied by any Lisbon institute is 3 (A,B and C all study 3 deseases) so the table would look like this

Z - 3
X - 3
M - 3

Edit : I managed to found a way to do it. Here is the query that I came up with

SELECT DISTINCT D.name, COUNT(*) AS C
FROM desease D, studies E, institute I
WHERE I.iid = E.iid AND D.did = E.did AND I.city = "Lisboa"
GROUP BY D.name
HAVING C >= ALL (
SELECT COUNT(*)
FROM desease D, studies E, institute I
WHERE I.iid = E.iid AND D.did = E.did AND I.cidade = "Lisboa"
GROUP BY D.name

);

omg you need to tell how to do that I tought I would have had to do that but I didnt find where to do it — Onica Radu, Nov 29 '11 at 21:00
Please rephrase your question. I didn't understand what exactly do You need. — Filip Popović, Nov 29 '11 at 21:05
An institute studies a desease. Imagine The institute from Lisbon that studies the most diseases. Make X this number of diseases. I need the names of the diseases that are beeing studied by X institutes from Lisbon. So Il get a table (D.name, X) — Onica Radu, Nov 29 '11 at 21:10
It would help if you included example data and the expected result. — hochl, Nov 29 '11 at 21:17
You really do not need `DISTINCT` in your final query. Grouping by `D.name` makes the resulting rows distinct enough. — Andriy M, Nov 30 '11 at 20:56

score 0 · Answer 1 · answered Nov 29 '11 at 21:09

0

I don't understand structure/problme well enough but I did see that you were mixing joins and had a cross join which would inflate the number of recrds.

SELECT DISTINCT D.name, MAX(I.iid)
FROM desease D
INNER JOIN  studies S ON D.iid=S.Did
INNER JOIN  institute I ON (S.iid = I.iid)
WHERE I.city = 'Lisboa' AND D.did = S.did
GROUP BY D.nome
HAVING COUNT(I.iid) = MAX(I.city)

answered Nov 29 '11 at 21:09

xQbert

34,733
2
41
62

You have writen Lisboa instead of Lisbon. If your portuguese here is the question in portuguese : Quais os nomes das Doenças estudadas pelo maior número de Institutos de Lisboa? Pretende-se uma lista com o nome da Doença e o n.º exacto de Institutos que estudam essa doença. (this is the exact questin I just translated it to english) – Onica Radu Nov 29 '11 at 21:13
This looks to me like the right answer but it doesnt give me the correct results X( it has to be something close to this ... Il try experimenting a little – Onica Radu Nov 29 '11 at 21:39

hochl · Answer 2 · 2011-11-29T21:40:28.933

Just a rough guess what you need:

SELECT stu.iid, COUNT(*) AS nstudies
FROM studies stu, institute ins
WHERE stu.iid=ins.iid
AND ins.city='Lisboa'
GROUP BY stu.iid
ORDER BY nstudies DESC;

This should give you a list of institutes that are in Lisboa and the number of studies they did.

SELECT stu.did, COUNT(*) AS ninst
FROM studies stu, institute ins, disease dis
WHERE stu.iid=ins.iid
AND stu.did=dis.did
AND ins.city='Lisboa'
GROUP BY stu.did
ORDER BY ninst DESC;

This gives you a list of deseases and the number of Lisboa instutitues that did it.

Unfortunately your question leaves a lot of room for speculation as to what you need -- maybe you should add some example data and the expected result.

kevev22 · Answer 3 · 2011-11-29T21:28:26.640

This would return a list of disease names that have an institute in Lisbon starting with the one with the greatest number of institutes in Lisbon and going down:

SELECT D.name, COUNT(*) as numberOfInstitutes
FROM desease D
INNER JOIN studies S ON D.did=S.did
INNER JOIN institute I ON (S.iid = I.iid)
WHERE I.city = 'Lisbon'
GROUP BY D.did
ORDER BY COUNT(*) desc

If you need only the one that has the most institutes and you need the rest of the columns from the desease table, you can do this (in Sql Server):

SELECT TOP 1 D.* 
FROM desease D
INNER JOIN
(
    SELECT D.did, COUNT(*) as numberOfInstitutes
    FROM desease D
    INNER JOIN studies S ON D.did=S.did
    INNER JOIN institute I ON (S.iid = I.iid)
    WHERE I.city = 'Lisbon'
    GROUP BY D.did
) as tblCount on tblCount.did = D.did
ORDER BY numberOfInstitutes desc

SQL Query - count - max

3 Answers3