CODEWITHSUNDEEP
mysqlsql
Mark
Mark

Reputation: 2760

get row count in single query in mysql

I have a table in that I want to get the rowcount of records in each column. where column1 is not null column2 is not null column3 is notnull and uID='2'

if the record is like

uid   C1    C2    C3
2    Null   3     3
2    2      2     Null

the count here is C1=1, c2=2,c3=1 How can I do this in one query

Upvotes: 1

Views: 281

Answers (4)

Niels Doucet
Niels Doucet

Reputation: 352

select count(C1), count(C2), count(C3) from table

Upvotes: 0

Patrick Desjardins
Patrick Desjardins

Reputation: 140753

SELECT COUNT(C1), COUNT(C2), COUNT(C3)
FROM Mytable 
WHERE uid=2

By default, it won't count NULL value.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135789

SELECT COUNT(C1), COUNT(C2), COUNT(C3)
    FROM YourTable
    WHERE uID = 2
    GROUP BY uID

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270609

COUNT(colname) should ignore NULL values in the aggregate, so the query should actually be simple. Note that COUNT(*) behaves differently, and does not ignore NULL rows.

SELECT COUNT(C1), COUNT(C2), COUNT(C3) FROM table WHERE uid=2

More information on the NULL aggregate behaviors is found here in the docs.

Upvotes: 2

Related Questions