CODEWITHSUNDEEP
mysql
Jack Pato
Jack Pato

Reputation: 51

Count values of MySQL

I have my MySQL database with the data below.

I want to count how many times did john paid.

ID NAME NUMBER COUNTRY STATUS 
----------------------------------
1  JOHN  021    USA    PAID
2  MIKE  022    CA     UNPAID
3  KENT  023    UK     NOT-OPEN
4  JOHN  021    USA    PAID

My query:

SELECT COUNT(*) FROM USER WHERE NUMBER = '021' OR NAME = 'JOHN' HAVING STATUS = 'PAID'

But am getting this error:

Unknown column 'STATUS' in 'having clause'

Upvotes: 1

Views: 62

Answers (3)

PrakashG
PrakashG

Reputation: 1642

You can also do like this: 

SELECT COUNT(*) FROM USER WHERE (NUMBER = '021' OR NAME = 'JOHN') AND (STATUS = 'PAID')

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

Having work on result .. you have not the column STATUS in you resul

or you add to the WHERE clause (and avoid the having) 

SELECT COUNT(*) 
FROM USER 
WHERE ( NUMBER = '021' OR NAME = 'JOHN' )
AND STATUS = 'PAID'

or you add the column to select (but this require for mysql version > 5.6 a group by to fr default sql_mode setting )

SELECT COUNT(*), STATUS  
FROM USER 
WHERE NUMBER = '021' OR NAME = 'JOHN' 
GROUP BY STATUS 

HAVING STATUS = 'PAID'

Upvotes: 1

Michael Tétreault
Michael Tétreault

Reputation: 424

Replace HAVING by AND

Also to make sure that you only get the count of this single user, you also need to put

(NUMBER = '021' AND NAME = 'JOHN')

Try this:

    SELECT COUNT(*) FROM USER WHERE (NUMBER = '021' AND NAME = 'JOHN') AND STATUS = 'PAID'

Upvotes: 0

Related Questions