I hope someone can help.
I have been attempting to tackle this problem for some time. I have two tables “admissions” and “month data”.
The admissions table contains the two fields “admission_date” and “admission_id” – ID id the value that is counted as well as “centre_id” for filtering purposes. What wish to be able to do is count all the admissions for any given month, for any given centre_id, and display them. If there is a 0 value, just display 0. I need to do this for each year.
So a 2024 query would display all the records for centre = 2 and show the admission_ids counted in each month for 2024 or return a 0 if none exist.
This is the code I have been using.
SELECT MONTHNAME(month) MONTH, COUNT(admission_id) COUNT
FROM rescue_month_data
LEFT JOIN rescue_admissions
ON EXTRACT(YEAR_MONTH FROM rescue_month_data.month) = EXTRACT(YEAR_MONTH FROM rescue_admissions.admission_date)
WHERE rescue_admissions.centre_id = 1 AND YEAR(month)=2023 AND rescue_admissions.admission_date IS NULL
GROUP BY MONTH(month)
ORDER BY MONTH(month)
For some reason, unbeknownst to me, the query returns all the months back EXCEPT may and June. These months are present in the month data table AND there are admissions for those months too. There must be a problem somewhere but I’m a novice at this sort of think and not sure how to adapt this query to make it work.
I’m not sure if the query needs to be done at the centre_id level first then do the admissions/date stuff on that data.
I would really appreciate the help if anyone can.
Kind regards & many thanks
Dan
Table: month data
Columns: month_id, month_name (txt), month (date), count (int)
data:
1, January-23, 01 January 2023, 0
2, February-23, 01 February 2023, 0
3, March-23, 01 March 2023, 0
4, April-23, 01 April 2023, 0
5, May-23, 01 May 2023, 0
6, June-23, 01 June 2023, 0
7, July-23, 01 July 2023, 0
8, August-23, 01 August 2023, 0
9, September-23, 01 September 2023, 0
10, October-23, 01 October 2023, 0
11, November-23, 01 November 2023, 0
12, December-23, 01 December 2023, 0
Table admissions
Columns: admission_id, centre_id, admission_date
Data:
1, 1, May 31 2023 8:49 am
2, 2, May 31 2023 10:49 am
3, 2, June 31 2023 8:49 am
4, 1, June 31, 2023 8:49 am
5, 1, July 31, 2023 8:49 am
6, 1, May 31, 2023 8:49 am
7, 2, August 31, 2023 8:49 am
8, 2, August 31, 2023 11:49 am
9, 2, December 31, 2023 8:49 am
10, 2, December 31, 2023 8:49 am
11, 1, December 31, 2023 8:49 am
(in the actual table the above data continues in the same way for 2024)
Desired output for query centre_id=2 and year 2023 should be:
| month | t |
|---|---|
| January | 0 |
| February | 0 |
| March | 0 |
| April | 0 |
| May | 1 |
| June | 1 |
| July | 0 |
| August | 2 |
| September | 0 |
| October | 0 |
| November | 0 |
| December | 2 |
Dan Lindley is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
As per the comments, WHERE clauses are asserted after the OUTER JOIN.
Either put the filtering in the join, or a sub query.
Also…
- you should always fully qualify references (specify which table a column is from)
- don’t use keywords as aliases
SELECT
MONTHNAME(m.month) MONTH_NAME,
COUNT(a.admission_id) COUNT_ADMISSIONS
FROM
rescue_month_data AS m
LEFT JOIN
rescue_admissions AS a
ON EXTRACT(YEAR_MONTH FROM m.month) = EXTRACT(YEAR_MONTH FROM a.admission_date)
AND a.centre_id = 1
-- AND a.admission_date IS NULL
-- this has been removed as it makes no sense to me
WHERE
YEAR(m.month)=2023
GROUP BY
MONTH(m.month)
ORDER BY
MONTH(m.month)
Or…
SELECT
MONTHNAME(m.month) MONTH_NAME,
COUNT(a.admission_id) COUNT_ADMISSIONS
FROM
rescue_month_data AS m
LEFT JOIN
(
SELECT
*
FROM
rescue_admissions
WHERE
centre_id = 1
-- AND admission_date IS NULL
-- this has been removed as it makes no sense to me
)
AS a
ON EXTRACT(YEAR_MONTH FROM m.month) = EXTRACT(YEAR_MONTH FROM a.admission_date)
WHERE
YEAR(m.month)=2023
GROUP BY
MONTH(m.month)
ORDER BY
MONTH(m.month)
3