mysql count problem

**newphpcoder** · Feb 21, 2012 04:37

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:

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

**r937** · Feb 21, 2012 05:00

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

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

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

**newphpcoder** · Feb 22, 2012 18:30

here is my revised code :

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

**r937** · Feb 22, 2012 18:40

Originally Posted by newphpcoder

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

use this pattern --

Code:

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

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 --

Code:

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

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

**newphpcoder** · Feb 22, 2012 18:50

I tried this :

Code:

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

**newphpcoder** · Feb 22, 2012 18:59

I also tried this one.

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 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

**r937** · Feb 22, 2012 19:30

perhaps it is a language problem

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

**newphpcoder** · Feb 22, 2012 19:33

So what should I do?

Thank you

**r937** · Feb 22, 2012 20:09

Originally Posted by newphpcoder

So what should I do?

re-read my posts

the answer is in there

**newphpcoder** · Feb 22, 2012 21:13

I have this code:

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:

Code:

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

**newphpcoder** · Feb 22, 2012 21:26

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:

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.

Code:

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

**r937** · Feb 22, 2012 23:09

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

good luck

User Tag List

Thread: mysql count problem

Thread Tools

Display

mysql count problem

Bookmarks

Bookmarks

Posting Permissions