MySQL query w/ COUNT() and HAVING based on date comparison

Question

I am trying to limit my count results to those that meet a specific criteria. I am currently using a WHERE clause and my output is as expected.

Here is my current query, and as you can see *ads_list* and *ads_cate* records will only be fetched if my WHERE recdate meets the 14 day mark:

$queryCats = "
SELECT ads_cate.cateName, COUNT(ads_list.Title)
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
WHERE to_days(now())<=(to_days(recdate)+14)
GROUP BY ads_cate.cateName";

I would like all categories and ads retrieved, but counted ONLY when they meet my WHERE clause criteria. I have been using the HAVING with 0 luck like below. Maybe there is a better way?

$queryCats = "
SELECT ads_cate.cateName, ads_list.recdate, COUNT(ads_list.Title)
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
GROUP BY ads_cate.cateName
HAVING to_days(now())<=(to_days(recdate)+14)";

The problem is my categories are not in my array to display in my results set. they are being filtered. I need all category names available in my array regardless of they meet that criteria or not. — OldWest
– OldWest, Commented Jun 23, 2011 at 20:06

Joe Stefanelli · Accepted Answer · 2011-06-23 20:07:51Z

3

Try using a LEFT JOIN and make the date test part of the join condition:

SELECT ac.cateName, COUNT(al.Title)
    FROM ads_cate ac
        LEFT JOIN ads_list al
            ON ac.id = al.category 
                AND to_days(now())<=(to_days(al.recdate)+14)
    GROUP BY ac.cateName

answered Jun 23, 2011 at 20:07

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

3 Comments

OldWest Over a year ago

Joe, your sample was magic. I am guessing the al. Your presentation and approach is new to me such as the al. abbreviation on the LEFT JOIN.. I am guessing the al.Title (al portion) assigns the table that alias. Thanks for the interesting code.

Joe Stefanelli Over a year ago

You guessed right. The al alias is just a short hand way of referencing the ads_list table throughout the query. The alias is actually assigned when the table is referenced during the LEFT JOIN ads_list al portion of the code.

OldWest Over a year ago

I see what you mean in that "ads_list al" fragment. Thanks for the helpful lesson.

Aziz Shaikh · Accepted Answer · 2011-06-23 20:26:35Z

1

This may work:

SELECT ads_cate.cateName, ads_list.recdate, COUNT(CASE WHEN to_days(now())<=to_days((recdate)+14) THEN ads_list.Title ELSE NULL END) AS Title
FROM
ads_cate
JOIN ads_list ON ads_cate.id = ads_list.category 
GROUP BY ads_cate.cateName

edited Jun 23, 2011 at 20:26

answered Jun 23, 2011 at 20:12

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

Try with ELSE NULL, not ELSE 0 because COUNT() counts everything that is not NULL.

Don Kirkby · Accepted Answer · 2011-06-23 20:10:25Z

0

Put your filtered counting in a subquery and then LEFT JOIN from the category table to the subquery.

answered Jun 23, 2011 at 20:10

Don Kirkby

56.7k28 gold badges225 silver badges307 bronze badges

Collectives™ on Stack Overflow

MySQL query w/ COUNT() and HAVING based on date comparison

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related