By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

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

P: 17
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
Share this Question
Share on Google+
1 Reply


iburyak
Expert 100+
P: 1,017
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.