472,958 Members | 2,382 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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 1923
1,017 Expert 512MB
Try this:

Expand|Select|Wrap|Line Numbers
  1. declare  @a table (CrDate datetime,  Dishes int) 
  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)
  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

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

Similar topics

by: Frank Natoli | last post by:
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise, of "abc". Is there any simple way to inject all the rows of "abc" into "xyz"? Tried "insert into xyz select * from abc"...
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
by: satish | last post by:
Values of two columns in two different tables--presentation using select Hi Everyone, i have two tables in the database . One is called address table and one is adressPhone Table. Below...
by: Peter | last post by:
People are telling me it is bad to put select * from <atable> in a view. I better should list all fields of the table inside the definition of the view. I dont know exactly why but some...
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
by: =?Utf-8?B?UmljaCBIdXRjaGlucw==?= | last post by:
I'm not really sure how to ask this question because I'm still getting my feet wet with data access and VB.NET, but here goes: To start off with, I'm using VB 2005 Express to connect to an Access...
by: Rickster66 | last post by:
As Instructed this is a new thread regarding my original post: "Select Only 10 Columns Going Back" I'm sorry for the late response. I've been gathering up information and carefully with as much...
by: padmaneha | last post by:
Hi I have created two tables which are movies table and videos table Columns of movies table are movie id, movietitle, moviereviewid and the select statement which I have written to fetch...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.