Complicated COUNT() Command. Confused!

Any help will be very much appreciated.

Here's my query:

"SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count
FROM tblmake
LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid
GROUP BY tblmake.makid
ORDER BY tblmake.make ASC";

Code (markup):

How do I incorporate the following condition in the COUNT:

(adddate >= CURDATE() OR adddate is NULL)

Code (markup):

In other words, how do I get it to count only the tblmodel.modelids whose adddates are greater or equal to the current date or whose tblmodel.modelids are NULL?

 

well since the adddate is on the joined table you could add this filter on the join

SELECT tblmake.make, tblmake.url, COUNT(tblmodel.modelid) AS model_count
FROM tblmake
LEFT JOIN tblmodel ON tblmake.makid = tblmodel.makeid
AND (tblmodel.adddate >= CURDATE() OR isnull(tblmodel.adddate,0) = 0)
GROUP BY tblmake.makid
ORDER BY tblmake.make ASC

Code (sql):

(the above isnull is mssql syntax..)

 

Thanks for the effort, but that doesn't work. I've already tried it. I have to make it a part of the COUNT function somehow.

Anyone know how?

 

Not sure why it does not work...
Error ? or wrong results ?

Try another more explicit way

SELECT 
	tblmake.make, 
	tblmake.url, 
	( 
		SELECT 
			count(modelid) 
		FROM 
			tblmodel 
		WHERE 
			(adddate >= CURDATE() OR adddate is null) 
			AND 
			tblmake.makeid = tblmodel.makeid 
		GROUP BY 
			tblmodel.makeid 
	) AS model_count
FROM 
	tblmake 
ORDER BY 
	tblmake.make ASC

Code (sql):

a question: you sure you want the models that were made after today ? (future models ?) (had to ask )

hope this does the trick

 

Thanks, Gnp! It worked.

One reputation point for you!

 

Glad it worked !

thanks for the rep