CODEWITHSUNDEEP
phpmysqlsql
sukumar K.G
sukumar K.G

Reputation: 1

How to get count of multiple column using sql

   My table like
   ---------------
      trtm       t_date          id_no  certno1   certno2  certno3  certno4  certno5  certno6 certno7 certno8 certno9  certno10 
       TM       15/02/2002       A12      2158
       TM       15/02/2010       A13      8181     8182     8183     8184     8185     8186     8187     8188      8189     8190
       TM       15/02/2010       A14      19138
      -------------------

I need to take count of id_no and certno from 1 to 10

   my query is
   ----------
  select count(id_no) as total_id,count(CONCAT(certno1, certno2, certno3,certno4,certno5,certno6,certno7,certno8,certno9,certno10)) as certificate from table where trtm='TM'
  ----------
  my output is:
   ---------
   total_id      certificate
       3             3
  -------------------------

but i need the count of certificate where the values are there is 3 but i need is 12

  ------------ 
  output needs:
  -----------
  total_id      certificate
       3             12
  -------------

i am getting only the count in both total_id and certificate.so, please help me if anybody knows.

Upvotes: 0

Views: 55

Answers (2)

Shaharyar
Shaharyar

Reputation: 12439

Count separately and then SUM it:

SELECT 
    count(id_no) as total_id,
    (count(certno1) +
        count(certno2) +
        count(certno3) +
        count(certno4) +
        count(certno5) +
        count(certno6) +
        count(certno7) +
        count(certno8) +
        count(certno9) +
        count(certno10)) as certificate 
FROM table 
WHERE trtm='TM';

Upvotes: 1

Marc B
Marc B

Reputation: 360592

Assuming the "empty" certX fields are nulls, you'd need something like this:

SELECT (cert1 is not null) + (cert2 is not null) + ... (cert10 is not null)

Each is not null test will return a boolean true/false, which will be typecast to integers by mysql, and converted into a basic 1+1+0+1+....-type addition operation.

Upvotes: 2

Related Questions