PHP Count MySQL value and ignore duplicate

Question

I'm having difficulties counting how many times it finds a new personnel_id in table planning but ignoring if it finds the same personnel_id a second time.

Exemple personnel_id 24 is on building A and building B next week but count only as 1 because this is the same person.

In the end I would do something like $result = $totalpersonnel_id_tablepersonnel - $totalpersonnel_id_tableplanningand it would give me a number like 7 meaning 7 employees has no building affected.

I think this is the correct query for what I want:

$test=mysql_query("SELECT COUNT(DISTINCT personnel_id) AS numberpid 
FROM planning WHERE personnel_id!='' GROUP BY personnel_id HAVING 
COUNT(personnel_id) > 1");

I don't find how to get the sum from this query.

Chanom First · Accepted Answer · 2015-06-09 08:57:56Z

1

if personnel_id!='' mean NOT NULL use personnel_id IS NOT NULL instead and having use numberpid > 1 so query after edited below

SELECT COUNT(DISTINCT personnel_id) AS numberpid 
FROM planning WHERE personnel_id IS NOT NULL 
GROUP BY personnel_id 
HAVING numberpid > 1

answered Jun 9, 2015 at 8:57

Chanom First

1,1461 gold badge12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BackTrack57 Over a year ago

I've now tested your solution and it's returning no result from PhpMyAdmin. I think it's because it's missing a COUNT in HAVING. If I add <code>HAVING count(numberpid) > 1</code> I get a list of numberpid=1. That looks correct but is there a mysql_result or something else for it COUNT all those numberpid=1 displayed ? Maybe a <pre>while</pre> to do ? I'm a bit lost

Auris · Accepted Answer · 2015-06-09 09:06:28Z

0

you would need to use a subquery to filter, group and count by personnel_id, and filter the count in the parrent query.

SELECT personnel_id, numberpid 
FROM (
    SELECT count(personnel_id) as numberpid, personnel_id 
    FROM palnning 
    WHERE personnel_id!='' 
    GROUP BY personnel_id) as tmp_table 
WHERE numberpid > 1

answered Jun 9, 2015 at 9:06

Auris

1,3291 gold badge9 silver badges18 bronze badges

3 Comments

BackTrack57 Over a year ago

That query shows a list of personnel_id who has numberpid=2. Is it your method to remove the number of numberpid=2 found ? Exemple it display 3 numberpid=2 then $totalpersonnel_id_tableplanning - 3 = correct result ?

Auris Over a year ago

Hi, not sure I understand your question. What the query returns is:personnel_id (id's that you are gtrying to filter),

Auris Over a year ago

Sorry. Hi, not sure I understand your question. What the query returns is: personnel_id (id's that you are gtrying to filter), numberpid (how many entries for that id is found, this is used to filter the one that are greater than 1 as in your initial query). If you want to count how many unique entries you have, change the parent query's SELECT count(personnel_id) as total_unique this will give you the total count of unique personnel_id's that are mentioned more than 1 time. (if you want to remove "more than 1 time" filter, simply remove the WHERE part of the parent query.

BackTrack57 · Accepted Answer · 2015-06-09 16:58:30Z

0

Found the solution :

$result=mysql_query("SELECT COUNT(*) AS numberpid FROM planning WHERE personnel_id!='' GROUP BY personnel_id"); $howmany=mysql_num_rows($result);

Thanks for help !

answered Jun 9, 2015 at 16:58

BackTrack57

3951 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

PHP Count MySQL value and ignore duplicate

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related