How to count sum of three columns MySql

Question

I have three columns with names:

 projectNo| process | procLeader | procCheker  |     Stuff     |
----------+---------+------------+-------------+---------------+
 16090001 | ANM     | ben        | barry       | bob, bart, bok| 
 16090001 | BLD     | anton      | kirill      | kart, ali     |

What I want to is to count procLeader, procChecker, stuff columns assigned to projectNo. I managed to count each column by using query:

SELECT 
    COUNT(procLeader) AS `ld`, 
    COUNT(procChecker) AS `ch`, 
    SUM((LENGTH(stuff) - LENGTH(REPLACE(stuff,",","")) + 1)) AS `st` 
FROM `process` 
WHERE projectNo=16090001;

I get

 ld| ch | st |
---+----+----+
  2|   2|   5|

I need something like 'total' table

How I can sum this values? or maybe use another method?

SO is littered with questions like this from people who forgot to normalize their databse. — e4c5, Sep 30 '16 at 05:02
I guess, you are trying to sum up the ch and st column. Is it? — AT-2017, Sep 30 '16 at 06:25

score 3 · Accepted Answer · edited Sep 30 '16 at 06:13

3

SELECT 
COUNT(procLeader) AS `ld`, COUNT(procCheker) AS `ch`, 
SUM((LENGTH(stuff) - LENGTH(REPLACE(stuff,",","")) + 1)) AS `st` ,
(
   COUNT(procLeader) + 
    COUNT(procCheker) +
    SUM((LENGTH(stuff) - LENGTH(REPLACE(stuff,",","")) + 1)) 
 ) As `Total` 
 FROM `process` WHERE projectNo=16090001

Please let us know if you have any concerns or que.

edited Sep 30 '16 at 06:13

AT-2017

3,114
3
23
39

answered Sep 30 '16 at 05:44

Mr. Bhosale

3,018
1
17
34

getting this error `Unknown column 'procCheker' in 'field list` – Yevgeniy Bagackiy Sep 30 '16 at 06:14
oh the thing it not proCheker, there is a typo, should be procChecker. and it works thanks. – Yevgeniy Bagackiy Sep 30 '16 at 06:53
at table structure you mentioned 'procCheker' and in query you mentioned 'procChecker'. Query Edited. Thank U – Mr. Bhosale Sep 30 '16 at 07:11

AT-2017 · Answer 2 · 2016-09-30T06:50:42.163

1

Use the following query and it worked:

SELECT 
COUNT(procLeader) AS `ld`, COUNT(procChecker) AS `ch`, 
SUM((LENGTH(stuff) - LENGTH(REPLACE(stuff,",","")) + 1)) AS `st` ,
(
   COUNT(procLeader) + 
   COUNT(procChecker) +
   SUM((LENGTH(stuff) - LENGTH(REPLACE(stuff,",","")) + 1)) 
) As `Total` 
FROM `process` WHERE projectNo = 16090001

Output:

Id - ch - st - Total
2  - 2  - 5  - 9

edited Sep 30 '16 at 06:50

answered Sep 30 '16 at 06:44

AT-2017

3,114
3
23
39

now get problem, it works perfectly in phpmyadmin sql query, but when I put query in php code it shows `Parse error: syntax error, unexpected '"' ` Here is query in php `$result3 = $mysqli->query("SELECT (COUNT(procLeader) + COUNT(procChecker) + SUM((LENGTH(stuff) - LENGTH(REPLACE(stuff,",","")) + 1)) ) AS total FROM process WHERE projectNo = '$projectNo'");` Why so? – Yevgeniy Bagackiy Sep 30 '16 at 07:01
Oh i got the problem, nvm – Yevgeniy Bagackiy Sep 30 '16 at 07:10

score 0 · Answer 3 · answered Sep 30 '16 at 05:28

0

not sure I understand question, but if you want to count characters, did you try char_length and concat ?

SELECT char_length(concat(procleader, proccheker, REPLACE(stuff,',',''))) FROM process WHERE projectNo=16090001

answered Sep 30 '16 at 05:28

olatarkowska

41
1
4

I dont want to count characters, I need to count names – Yevgeniy Bagackiy Sep 30 '16 at 06:21

How to count sum of three columns MySql

3 Answers3