**Tables**

```
Patient (patient_id, patient_name, gender, staff_no, ward_no)

Ward (ward_no, ward_name, number_of_beds)

```

**Query**

For each ward that has at least one patient in it, give the ward number and name together with the number of patients in the ward and the number of empty beds. Your answer should be presented in decreasing order of empty beds.

I've written the following SQL code and the server gives me an error:

> 
> invalid use of an aggregate function
> 
> 
> 

```
SELECT
    ward.ward_no,
    ward.ward_name,
    COUNT(*) AS patient_count, 
    number_of_beds - COUNT(patient_id) AS empty_beds
FROM
    patient,
    ward
WHERE
    patient.ward_no = ward.ward_no
GROUP BY
    ward.ward_no,
    ward.ward_name,
    number_of_beds,
    empty_beds
HAVING
    COUNT(*) > 1
ORDER BY
    ward.ward_name

```

Can anyone help? I need to calculate a number of empty beds and return it as as a column.

Thanks in advance