468,316 Members | 2,023 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

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

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
Sep 23 '08 #1
1 1838
iburyak
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. declare  @a table (CrDate datetime,  Dishes int) 
  2.  
  3. insert into @a values ('2008-09-01 00:05:04.483', 1)
  4. insert into @a values ('2008-09-01 00:06:31.653', 1)
  5. insert into @a values ('2008-09-01 04:35:44.983', 1)
  6. insert into @a values ('2008-09-09 08:51:20.857', 1)
  7. insert into @a values ('2008-09-09 08:52:08.873', 1)
  8. insert into @a values ('2008-09-09 08:52:47.280', 1)
  9. insert into @a values ('2008-09-09 08:53:27.217', 1)
  10. insert into @a values ('2008-09-09 08:54:10.793', 1)
  11. insert into @a values ('2008-09-12 02:56:11.310', 1)
  12. insert into @a values ('2008-09-12 02:56:52.903', 1)
  13.  
  14. select convert(varchar(10),crdate,126), count(*)
  15. from @a
  16. where convert(varchar(10),crdate,126) between convert(varchar(10),'2008-09-01',126) 
  17. and convert(varchar(10),'2008-09-22',126) 
  18. Group by convert(varchar(10),crdate,126)
Sep 23 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Frank Natoli | last post: by
17 posts views Thread by kalamos | last post: by
33 posts views Thread by Peter | last post: by
22 posts views Thread by MP | last post: by
3 posts views Thread by =?Utf-8?B?UmljaCBIdXRjaGlucw==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.