CODEWITHSUNDEEP
mysql
Marko Mijailovic
Marko Mijailovic

Reputation: 431

MySQL count with a range condition

I have a query like this

select newscategory.CategoryName, count(distinct newsmain.id)
from newsmain join newscategory on 
newscategory.CategoryName = newsmain.Category 
group by CategoryName

and it is returning a correct results, like this:

CategoryName  count(distinct newsmain.id)
Acupunctura   1
Neurologie    1
Test Category 2

"newsmain" table has an "AppPublished" datetime field and what I'm trying to do is to add a condition to the count which will do the counting based on if that "AppPublished" is in the range of two datetime variables. For an example, I would need a result like this:

CategoryName  count(distinct newsmain.id)
Acupunctura   0
Neurologie    0
Test Category 1

Do I need to make a subquery or is there a way to add some condition to this query?

Because any added conditions in this query are resulting in unwanted filtering of the "CategoryName" column.

Upvotes: 0

Views: 44

Answers (1)

Rahul
Rahul

Reputation: 77876

You can use a CASE condition like

select newscategory.CategoryName, 
count(CASE WHEN AppPublished BETWEEN date1 and date2 THEN distinct newsmain.id END)
from newsmain join newscategory on 
newscategory.CategoryName = newsmain.Category 
group by CategoryName

(OR) like this

select newscategory.CategoryName, 
sum(CASE WHEN AppPublished BETWEEN date1 and date2 THEN 1 ELSE 0 END)
from newsmain join newscategory on 
newscategory.CategoryName = newsmain.Category 
group by CategoryName

Upvotes: 1

Related Questions