Version: mySQLi PHP 5.4.45
I'm trying to count the number of panels per unique ID. Instead, I'm getting the total number of tests per ID.
Before Query: (example)
eReq | panel_name | test
------------------------
500 | tox | Butabital
500 | tox | Amphetamine
500 | tox | MethAmp
500 | chem | CBC
600 | tox | Morphine
600 | chem | CBC
Query:
SELECT panel_name, count(*)
FROM `ordered_codes`
JOIN samples ON ordered_codes.tox_id = samples.tox_id
JOIN orders_codes ON ordered_codes.code_id = orders_codes.id
GROUP BY panel_name
Current Results:
panel_name | Count(*)
---------------------
tox | 4
chem | 2
Needed Results: (Numbers should be lower - counting panel_name by eReq)
panel_name | Count(*)
---------------------
tox | 2
chem | 2
I feel like this is very simple, I'm just overthinking at this point.
Are you looking for count(distinct)?
SELECT panel_name, count(distinct ereq)
FROM ordered_codes oc JOIN
samples s
ON oc.tox_id = s.tox_id
GROUP BY panel_name
I don't understand why there are two joins to ordered_codes, so I removed the second, assuming it is a typo. Your query will generate a syntax error.