Select query to know the Count of two columns in different tables

[padmaneha]

I need to fetch the count of Recipes posted on each day. For example if the following data is given

CrDate Dishes
2008-09-01 00:05:04.483 1
2008-09-01 00:06:31.653 1
2008-09-01 04:35:44.983 1
2008-09-09 08:51:20.857 1
2008-09-09 08:52:08.873 1
2008-09-09 08:52:47.280 1
2008-09-09 08:53:27.217 1
2008-09-09 08:54:10.793 1
2008-09-12 02:56:11.310 1
2008-09-12 02:56:52.903 1

Here is the query which I have written :
select fmp.crdate, count(f.dishes)as Recipescount
from food f inner join foodmemberpref fmp
on f.contentid=fmp.foodid where isapproved=1 and blogcid>0
and convert(varchar(10),fmp.crdate,126) between convert(varchar(10),'2008-09-01',126)
and convert(varchar(10),'2008-09-22',126)
group by f.dishes, fmp.crdate


Output should display

Crdate RecipesCnt
2008-09-01 00:05:04.483 3
2008-09-09 08:51:20.857 5
2008-09-12 02:56:11.310 2

[iburyak]

Try this:

declare  @a table (CrDate datetime,  Dishes int) 
 
insert into @a values ('2008-09-01 00:05:04.483', 1)
insert into @a values ('2008-09-01 00:06:31.653', 1)
insert into @a values ('2008-09-01 04:35:44.983', 1)
insert into @a values ('2008-09-09 08:51:20.857', 1)
insert into @a values ('2008-09-09 08:52:08.873', 1)
insert into @a values ('2008-09-09 08:52:47.280', 1)
insert into @a values ('2008-09-09 08:53:27.217', 1)
insert into @a values ('2008-09-09 08:54:10.793', 1)
insert into @a values ('2008-09-12 02:56:11.310', 1)
insert into @a values ('2008-09-12 02:56:52.903', 1)
 
select convert(varchar(10),crdate,126), count(*)
from @a
where convert(varchar(10),crdate,126) between convert(varchar(10),'2008-09-01',126) 
and convert(varchar(10),'2008-09-22',126) 
Group by convert(varchar(10),crdate,126)