Mysql count problem

newphpcoder · February 21, 2012, 10:37am

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

r937 · February 21, 2012, 11:00am

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

newphpcoder · February 23, 2012, 12:30am

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

r937 · February 23, 2012, 12:40am

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

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

newphpcoder · February 23, 2012, 12:50am

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

newphpcoder · February 23, 2012, 12:59am

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

r937 · February 23, 2012, 1:30am

perhaps it is a language problem

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

newphpcoder · February 23, 2012, 1:33am

So what should I do?

Thank you

r937 · February 23, 2012, 2:09am

re-read my posts

the answer is in there

newphpcoder · February 23, 2012, 3:13am

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

newphpcoder · February 23, 2012, 3:26am

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

r937 · February 23, 2012, 5:09am

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

good luck

Topic		Replies	Views
Problem in GROUP BY in UPDATE with CASE Databases	14	1301	September 24, 2014
MySQL COUNT if Column = 0 Databases mysql	9	6713	November 4, 2020
Get count on ALL rows even if 0 Databases	3	514	December 7, 2011
Update with + Operation Databases	3	507	September 23, 2014
COUNT columns containing NULL values (MSSQL) Databases	5	2573	September 24, 2014

Mysql count problem

Related topics