SELECT PRISON,
COUNT(DISTINCT FILE) AS FILE,
COUNT(DISTINCT CASE WHEN CNT = 2 THEN FILE ELSE NULL END) AS MULTIPLE
FROM
(
SELECT PRISON,FILE ,
COUNT(DISTINCT GENRE) AS CNT
FROM table
GROUP BY PRISON,FILE 
)t
GROUP BY PRISON

quote:

---

Originally posted by overboard22

alright, thing is,PRISON is in another table, so i have to do an inner join of that table with the one that has FILE. So im wondering where this bit goes in the query?
FROM dbo.Prisoner INNER JOIN
dbo.Prison ON dbo.Prisoner.Prison = dbo.Prison.CLAVEJ

SELECT PRISON,
COUNT(DISTINCT FILE) AS FILE,
COUNT(DISTINCT CASE WHEN CNT = 2 THEN FILE ELSE NULL END) AS MULTIPLE
FROM
?

after this should i put that inner join code or where? thank you so much for helping!

---

SELECT PRISON,
COUNT(DISTINCT FILE) AS FILE,
COUNT(DISTINCT CASE WHEN CNT = 2 THEN FILE ELSE NULL END) AS MULTIPLE
FROM
(
SELECT PRISON,FILE ,
COUNT(DISTINCT GENRE) AS CNT
FROM your join here 
GROUP BY PRISON,FILE 
)t
GROUP BY PRISON

quote:

---

Originally posted by shilpash

for the multiple,,,

here you go


WITH temp
AS (SELECT prison
,[file]
,COUNT(genre) AS genre
FROM table
GROUP BY PRISON
,[file]
HAVING COUNT(genre) > 1
)
SELECT prison
,COUNT(genre) AS multiple
FROM temp
GROUP BY prison

---

quote:

---

Originally posted by overboard22

ok ive tried both ways and i keep getting errors about not being able to reach the PRISON table. im trying something like this:

SELECT dbo.Prison.t.KEY, COUNT(DISTINCT File) AS File, COUNT(DISTINCT CASE WHEN CNT = 2 THEN File ELSE NULL END) AS MULTIPLE
FROM (SELECT dbo.Prison.KEY, dbo.Prisoner.File, COUNT(DISTINCT dbo.Prisoner.Genre) AS CNT
FROM dbo.PrisonerINNER JOIN
dbo.Prison ON dbo.Prisoner.Prison = dbo.Prison.KEY
GROUP BY dbo.Prison.KEY, dbo.Prisoner.File) AS t
GROUP BY dbo.Prison.t.KEY

and its telling me that it cant get the Prison.KEY data.. just wondering what im doing wrong? thanks again!

---