Below is my table called fittest. I need to find which students based
on student id (studid) have taken the pre and post test as designated in
the prepost column. So based on the simple table below I would need to
return studid 123456. How do I write the SELECT query for this?
SELECT studid
FROM fittest
GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2
;
SELECT studid
FROM fittest
GROUP BY studid
HAVING (MAX(prepost = 'pre' ) +
MAX(prepost = 'post')) = 2
AND COUNT(DISTINCT prepost) = 2;
Output
studid
123456
SELECT studid, prepost FROM `fittest` LIMIT 0, 30 ;
studid prepost
123456 pre
123456 post
1031460 pre
Solution:
CREATE TABLE fittest (`studid` int, `prepost` varchar(4));
INSERT INTO fittest(`studid`, `prepost`)
VALUES (123456, 'pre'),(123456, 'post'),(1031460, 'pre');
SELECT studid
FROM fittest
GROUP BY studid
HAVING COUNT(DISTINCT prepost) = 2
;
SELECT studid
FROM fittest
GROUP BY studid
HAVING (MAX(prepost = 'pre' ) +
MAX(prepost = 'post')) = 2
AND COUNT(DISTINCT prepost) = 2;
Output
studid
123456
0 comments:
Post a Comment