Counting rows in mysql?

I have a query which returns 20 rows.

select year( ArticleDate )
from NewsData nd
inner join NewsCategories nc on nd.DirectNewsID = nc.DirectNewsID
where nd.Deleted = 0
and year( nd.ArticleDate ) = 2006
group by nd.DirectNewsID;

Is it possible to get this to return a single row with the number 20 followed by the year in the and section?

The end query will not contain and year( nd.ArticleDate ) = 2006, so it will return a single row per year with the count. I only added the and year( n开发者_如何学编程d.ArticleDate ) = 2006 for testing purposes.

Is this possible?

Something like this?

select year(nd.ArticleDate), COUNT(DISTINCT nd.DirectNewsID)
from NewsData nd
inner join NewsCategories nc on nd.DirectNewsID = nc.DirectNewsID
where nd.Deleted = 0
group by year(nd.ArticleDate);

EDIT

This now counts the distinct occurances of nd.DirectNewsID in each year.

Try:

select year( nd.ArticleDate ), count(*)
from NewsData nd
inner join NewsCategories nc on nd.DirectNewsID = nc.DirectNewsID
where nd.Deleted = 0
and year( nd.ArticleDate ) = 2006
group by year( nd.ArticleDate );

Replace count(*) with count(distinct nd.DirectNewsID) if the objective is to determine number of DirectNewsID, instead of total number of rows.

You can use the count method. See: http://www.tizag.com/mysqlTutorial/mysqlcount.php

You can simply use count method to get the total number of rows. i.e.

select count(*) from NewsData nd inner join NewsCategories nc  where nd.Deleted = 0, nd.DirectNewsID =  c.DirectNewsID  and year( nd.ArticleDate ) = 2006  

Check it.. it should work as per your need