Query to fetch counting field values

Question

I have a MySQL table with 20 fields. out of 20, 15 have 3 possible value like 0,NA,1+. i need to write a query to fetch each field count which has value > 0. is it possible to get it in single query.?

Thanks Guys.

@danontheline.. It's just record count. I asked about field-wise count. — Act DEV
– Act DEV, Commented Nov 29, 2011 at 18:15

Álvaro González · Accepted Answer · 2011-11-29 19:22:38Z

1

Unless I'm missing something...

SELECT
    COUNT(CASE WHEN c1<>'0' THEN 1 END) AS c1_count,
    COUNT(CASE WHEN c2<>'0' THEN 1 END) AS c2_count,
    -- ...
    COUNT(CASE WHEN c15<>'0' THEN 1 END) AS c15_count
FROM t

answered Nov 29, 2011 at 19:22

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ajreal · Accepted Answer · 2011-11-29 18:28:14Z

0

select "column1", count(*) from your_table where value>0
union all
select "column2", count(*) from your_table where value>0
...
... // repeat until column20

interesting documentation :- http://dev.mysql.com/doc/refman/5.0/en/union.html

answered Nov 29, 2011 at 18:28

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Collectives™ on Stack Overflow

Query to fetch counting field values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related