计算每个不同id的不同值

Question

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.

Answer 1

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.