Mysql count problem

Good day!

Hi,

I encountered problem in Count.

I have this data:

REFNUM----LOT_CODE----PCODE–wip_chemicalweighing–wip_compounding-------wip_extrusion–wip_forming–wip_deflashing
000001----000001P35M–P35M—0.00------------------0.00------------------0.00-----------0.00---------40.00---------
000002----000002P35M–P35M—73.30-----------------NULL------------------NULL-----------NULL---------NULL---------

and I have this code:


SELECT PCODE, COUNT(LOT_CODE) AS lot_chemicalweighing, COUNT(wip_chemicalweighing + wip_compounding + wip_extrusion + wip_forming + wip_deflashing) AS virtual FROM kanban_data WHERE wip_chemicalweighing != '0.00' OR 'NULL' AND wip_compounding != '0.00' OR 'NULL' AND wip_extrusion != '0.00' OR 'NULL'AND wip_forming != '0.00' OR 'NULL' AND wip_deflashing != '0.00' OR 'NULL' GROUP BY PCODE;

And the output of this code is :

PCODE-- lot_chemicalweighing—virtual----
P35M----1----------------------0--------

the lot_chemicalweighing is correct but the virtual is wrong, because it should be 2 because I have 1 data in wip_chemicalweighing and 1 in wip_deflashing.

Thank you

you’ve got three different problems

for the benefit of other human beings who might want to read this thread, i will reformat your query

i strongly recommend that you always do the same, too – the mysql engine can read sql which is all strung out on one line, but if you want help from actual people, please format your sql to make it readable

SELECT PCODE
     , COUNT(LOT_CODE) AS lot_chemicalweighing
     , COUNT(wip_chemicalweighing + wip_compounding + wip_extrusion + wip_forming + wip_deflashing) AS virtual
  FROM kanban_data
 WHERE wip_chemicalweighing != '0.00'
    OR 'NULL'
   AND wip_compounding != '0.00'
    OR 'NULL'
   AND wip_extrusion != '0.00'
    OR 'NULL'
   AND wip_forming != '0.00'
    OR 'NULL'
   AND wip_deflashing != '0.00'
    OR 'NULL'
GROUP
    BY PCODE

okay, three things wrong…

if any of the column values is null, then wip_chemicalweighing + wip_compounding + wip_extrusion + wip_forming + wip_deflashing will be null

OR ‘NULL’ by itself is always true

whenever you mix ANDs and ORs, always use parentheses to ensure you get the correct sequence of evaluation

here is my revised code :


SELECT PCODE, COUNT(LOT_CODE) AS lot_chemicalweighing, (COUNT(wip_chemicalweighing) + count(wip_compounding) + count(wip_extrusion) + count(wip_forming) + count(wip_deflashing)) AS virtual FROM kanban_data

my problem now is in where clause to count only the non-zero and not null .

Thank you

use this pattern –


WHERE COALESCE(wip_chemicalweighing,0) <> 0
  AND COALESCE(wip_compounding,0) <> 0
  AND ...

you may have to read up about COALESCE to see how that works :slight_smile:

the next question is whether you really want all of them to be non-zero at the same time, in which case all of the COUNTs you have in the SELECT clause will be the same!

i suspect that what you actually want is simply to count all the non-zero values separately, in which case you don’t need those WHERE conditions

you would use the NULLIF function for that purpose –

SELECT COUNT(NULLIF(wip_chemicalweighing,0)) ...

you may have to read up about NULLIF to see how that works :slight_smile:

I tried this :


SELECT PCODE, COUNT(LOT_CODE) AS lot_chemicalweighing, (COUNT(count(NULLIF(wip_chemicalweighing, 0))) + (count(NULLIF(wip_compounding, 0))) + (count(NULLIF(wip_extrusion, 0))) + (count(NULLIF(wip_forming, 0))) +(count(NULLIF(wip_deflashing, 0)))) AS virtual FROM kanban_data WHERE (NULLIF(wip_chemicalweighing, 0)) GROUP BY PCODE;

and I got an error :
Error Code : 1111
Invalid use of group function
(16 ms taken)

Thank you

I also tried this one.


SELECT PCODE, COUNT(LOT_CODE) AS lot_chemicalweighing, (COUNT(wip_chemicalweighing) + count(wip_compounding) + count(wip_extrusion) + count(wip_forming) + count(wip_deflashing)) AS virtual FROM kanban_data WHERE (COALESCE(wip_chemicalweighing,0) <> 0) AND (COALESCE(wip_compounding,0) <> 0) AND (COALESCE(wip_extrusion,0) <> 0)
AND (COALESCE(wip_forming,0) <> 0) AND (COALESCE(wip_deflashing,0) <> 0) GROUP BY PCODE;

but no output.

Thank you

perhaps it is a language problem

i suggested that you ~not~ have those conditions in the WHERE clause

So what should I do? :frowning:

Thank you

re-read my posts

the answer is in there

:slight_smile:

I have this code:


UPDATE kanban_checker kc SET kc.count_wip_chemical_weighing = (SELECT COUNT(NULLIF(kd.wip_chemicalweighing, 0)) AS count_wip_chemical_weighing FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE),
         kc.virtual = (SELECT ((count(NULLIF(kd.wip_chemicalweighing, 0))) + (count(NULLIF(kd.wip_compounding, 0))) + (count(NULLIF(kd.wip_extrusion, 0))) + (count(NULLIF(kd.wip_forming, 0))) + (count(NULLIF(kd.wip_deflashing, 0)))) AS virtual FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE)

and it works, but I realize that I need to zero the count_wip_chemical_weighing if I already had data in wip_compounding.SO i tried this:


UPDATE kanban_checker kc SET kc.count_wip_chemical_weighing = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_compounding)), 0, COUNT(kd.wip_chemicalweighing)),2) AS count_wip_chemical_weighing FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_compounding = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_extrusion)), 0, COUNT(kd.wip_compounding)),2) AS count_wip_compounding FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_extrusion = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_forming)), 0, count(kd.wip_extrusion)),2) AS count_wip_extrusion FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_forming = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_deflashing)), 0, COUNT(kd.wip_forming)),2) AS count_wip_forming FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_deflashing = (SELECT ROUND(COUNT(kd.wip_deflashing),2) AS count_wip_deflashing FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE);

but it did not work.

Thank you

here is my situation.

in kanban_data table

I have fields:

wip_chemicalweighing
wip_compounding
wip_extrusion
wip_forming
wip_deflashing

first i entered data in wip_chemicalweighing,
when I entered in wip_compounding I need to 0.00 the wip_chemicalweighing and when I enter data in wip_extrusion the wip_compounding also become 0.00 and so on until the wip_deflashing has a data. which I resolved with this code:


UPDATE kanban_data kd SET wip_chemicalweighing = (SELECT ROUND(IF (NOT ISNULL(SUM(compounding)), 0, SUM(chemicalweighing)),2) AS wip_chemicalweighing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
         wip_compounding = (SELECT ROUND(IF (NOT ISNULL(SUM(extrusion)), 0, SUM(compounding)),2) AS wip_compounding FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
         wip_extrusion = (SELECT ROUND(IF (NOT ISNULL(SUM(forming)), 0, SUM(extrusion)),2) AS wip_extrusion FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
         wip_forming = (SELECT ROUND(IF (NOT ISNULL(SUM(deflashing)), 0, SUM(forming)),2) AS wip_forming FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM),
         wip_deflashing = (SELECT ROUND(SUM(deflashing),2) AS wip_deflashing FROM kanban k WHERE k.LOT_CODE = kd.LOT_CODE AND k.REFNUM = kd.REFNUM GROUP BY k.REFNUM)

now, I also need to do it in kanban_checker.

not SUM but COUNT, but it did not work.


UPDATE kanban_checker kc SET kc.count_wip_chemical_weighing = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_compounding)), 0, COUNT(kd.wip_chemicalweighing)),2) AS count_wip_chemical_weighing FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_compounding = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_extrusion)), 0, COUNT(kd.wip_compounding)),2) AS count_wip_compounding FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_extrusion = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_forming)), 0, count(kd.wip_extrusion)),2) AS count_wip_extrusion FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_forming = (SELECT ROUND(IF (NOT ISNULL(COUNT(kd.wip_deflashing)), 0, COUNT(kd.wip_forming)),2) AS count_wip_forming FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE), kc.count_wip_deflashing = (SELECT ROUND(COUNT(kd.wip_deflashing),2) AS count_wip_deflashing FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE);

Thank you so much

sorry, man, i have no idea what you’re trying to do

good luck