Error Code: 1242. Subquery returns more than 1 row in Attribute Subquery

I’m trying to get the difference between two dates deathtime and admittime using subquery in attribute list. This is the script that I run:

SELECT a.subject_id, a.hadm_id, a.admittime, a.deathtime, a.diagnosis, p.drug, 
(SELECT DATEDIFF(deathtime, admittime) FROM admissions WHERE deathtime <> '0000-00-00') AS diff FROM
    admissions AS a INNER JOIN prescriptions AS p
    ON a.subject_id = p.subject_id 
    WHERE diagnosis = 'CARDIAC ARREST';

It returns Error Code: 1242. Subquery returns more than 1 row when I run it. Would really appreciate any help.

Answer

The reason why you’re getting that error is because a subquery can’t return more than 1 result.

Try this:

SELECT     
a.subject_id,     
a.hadm_id,     
a.admittime,     
a.deathtime,     
a.diagnosis,     
p.drug,     
CASE WHEN a.deathtime <> '0000-00-00'THEN DATEDIFF(deathtime, admittime) AS Diff  
FROM
admissions AS a INNER JOIN prescriptions AS p
ON a.subject_id = p.subject_id 
WHERE diagnosis = 'CARDIAC ARREST';