0

how to count this

Hi, how to count value in table like this?

This just example:

SELECT COUNT(name1) AS newResult1 FROM table

As this code just count column name1 and it will give result of 3.

What I want to do is count value in name1, name2, name3 and it give result based on value yes or no. Thanks.

  • 1
    Possible duplicate of MySQL SUM with multiple GROUP BY on same table. Almost identical question, except for the table and column names. – ypercubeᵀᴹ Sep 24 '14 at 8:33
  • I think I get it wrong, you need row wise count for each column name1 name2 and name3? – vijayp Sep 24 '14 at 8:40
  • i want to count like this name1+name2+name3 WHERE id=1, but it's like SUM function. – dask Sep 24 '14 at 8:48
0
I guess this is what you are looking for

 CREATE TABLE  newResult1 
    ( name1 CHAR(3),
      name2 CHAR(3),
      name3 CHAR(3)
     )


    INSERT newResult1 (name1,name2,name3)
    SELECT 'yes','no','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'yes','yes','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'no','no','yes'

    INSERT newResult1 (name1,name2,name3)
    SELECT 'no','no','no'



Declare @countofTableRows INT ;

select @countofTableRows= Count(1) from newResult1;

select [name1yes], @countofTableRows - [name1yes]  [name1No]
       ,[name2yes], @countofTableRows -[name2yes]  [name1No]
       ,[name3yes], @countofTableRows - [name3yes] [name1No]
from
(
select sum(case name1 when 'yes' then 1 else 0 end ) [name1yes]
,sum(case name2 when 'yes' then 1 else 0 end ) [name2yes]
,sum(case name3 when 'yes' then 1 else 0 end ) [name3yes]
from newResult1
)Resetset;
  • thanks, but how about 'no' i want to count this to. – dask Sep 24 '14 at 10:14
  • I updated my answer. – AA.SC Sep 24 '14 at 10:31
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Declare @countofTableRows int select @countofTableRows= Count(1) from newResu' at line 1 – dask Sep 24 '14 at 10:45
  • did MySQL server error? – dask Sep 24 '14 at 10:45
  • Actually I wrote script in SQL Server, – AA.SC Sep 24 '14 at 15:10
0

Separate Counts

SELECT
    SUM(IF(name1='yes',1,0)) YesCount1,
    SUM(IF(name1='no' ,1,0)) NoCount1,
    SUM(IF(name2='yes',1,0)) YesCount2,
    SUM(IF(name2='no' ,1,0)) NoCount2,
    SUM(IF(name3='yes',1,0)) YesCount3,
    SUM(IF(name3='no' ,1,0)) NoCount3
FROM table;

Combined Counts

SELECT
    YesCount1 + YesCount2 + YesCount3 YesCount,
    NoCount1  + NoCount2  + NoCount3  NoCount
FROM
(
    SELECT
        SUM(IF(name1='yes',1,0)) YesCount1,
        SUM(IF(name1='no' ,1,0)) NoCount1,
        SUM(IF(name2='yes',1,0)) YesCount2,
        SUM(IF(name2='no' ,1,0)) NoCount2,
        SUM(IF(name3='yes',1,0)) YesCount3,
        SUM(IF(name3='no' ,1,0)) NoCount3
    FROM table
) A;

Both

SELECT
    YesCount1,YesCount2,YesCount3,
    YesCount1+YesCount2+YesCount3 YesCount,
    NoCount1,NoCount2,NoCount3,
    NoCount1+NoCount2+NoCount3 NoCount
FROM
(
    SELECT
        SUM(IF(name1='yes',1,0)) YesCount1,
        SUM(IF(name1='no' ,1,0)) NoCount1,
        SUM(IF(name2='yes',1,0)) YesCount2,
        SUM(IF(name2='no' ,1,0)) NoCount2,
        SUM(IF(name3='yes',1,0)) YesCount3,
        SUM(IF(name3='no' ,1,0)) NoCount3
    FROM mytable
) A;

Not the answer you're looking for? Browse other questions tagged or ask your own question.