CODEWITHSUNDEEP
mysqlsql
morne
morne

Reputation: 4189

aggregate sql with a where clause

Im having trouble with this query.

SELECT adm_Consultant, count(adm_Consultant) as num
FROM Admission
WHERE  adm.adm_ReferralDate >= '01/01/2014 00:00:00' AND adm.adm_ReferralDate <= '31/12/2014 00:00:00'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
GROUP BY adm_Consultant

ERROR: General error

this works though, but returns the null values as-well 

SELECT adm_Consultant, count(adm_Consultant) as num
FROM Admission
GROUP BY adm_Consultant

I tried the HAVING clause instead of the WHERE clause, but still it fails.

Please help.

here was my reading material.

COUNT(expr)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

Upvotes: 0

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Try using ISO standard date formats:

SELECT adm_Consultant, count(adm_Consultant) as num
FROM Admission adm
WHERE  adm.adm_ReferralDate >= '2014-01-01' AND adm.adm_ReferralDate <= '2014-12-31' AND
       adm.adm_PriorSurgery = 'Yes' AND
       adm.adm_Consultant <> ''
GROUP BY adm_Consultant;

Upvotes: 2

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

You are forgetting to create alias adm 

SELECT adm_Consultant, count(adm_Consultant) as num 
FROM Admission adm
WHERE  adm.adm_ReferralDate >= '01/01/2014 00:00:00' AND 
adm.adm_ReferralDate <= '31/12/2014 00:00:00'
AND adm.adm_PriorSurgery = 'Yes'
AND adm.adm_Consultant <> ''
GROUP BY adm_Consultant

Upvotes: 2

Related Questions