Combining 2 SQL SUM queries with date as the join

I have 2 queries that work fine individually.  However I would like to combine these queries using the date as a join. I.e. I would like to find out totals each day for both these tables and return them in one query
Date...Total1....Total2

I would like to return 0, if there was not a total in one table.

My queries are:

SELECT    SUM(Amount) AS Total, CONVERT(datetime, CONVERT(varchar, RowDate, 3)) AS TheDate
FROM         dbo.tbl_Course
WHERE     (TypeID = 2)
GROUP BY CONVERT(datetime, CONVERT(varchar, RowDate, 3))
ORDER BY TheDate DESC

SELECT    SUM(Amount) AS Total, CONVERT(datetime, CONVERT(varchar, LogDate, 3)) AS TheDate
FROM         dbo.tbl_Register
GROUP BY CONVERT(datetime, CONVERT(varchar, LogDate, 3))
ORDER BY TheDate DESC


Any suggestions?
LVL 4
JohnHowlettAsked:
Who is Participating?
 
Jai SConnect With a Mentor Tech ArchCommented:
change it like this

SELECT theDate,dbo.GetTotal(TheDate),dbo.GetTotal2(TheDate)
FROM (SELECT CONVERT(datetime, CONVERT(varchar, RowDate, 3)) AS TheDate
FROM         dbo.tbl_Course
WHERE     (TypeID = 2)
GROUP BY CONVERT(datetime, CONVERT(varchar, RowDate, 3))
UNION ALL
SELECT    CONVERT(datetime, CONVERT(varchar, LogDate, 3)) AS TheDate
FROM         dbo.tbl_Register
GROUP BY CONVERT(datetime, CONVERT(varchar, LogDate, 3))) as A


The GetTotalfunction will have
Create function GetTotal(@date1 datetime)
returns int
as
select count(*) from dbo.tbl_Course where date = @date1

and GetTotal2 as
Create function GetTotal2(@date1 datetime)
returns int
as
select count(*) from dbo.tbl_Register where date = @date1
0
 
Jai STech ArchCommented:
you cannot have them as separate columns becos you use group by
you can make a union where in you will get the result as one output
like this

SELECT    SUM(Amount) AS Total, CONVERT(datetime, CONVERT(varchar, RowDate, 3)) AS TheDate
FROM         dbo.tbl_Course
WHERE     (TypeID = 2)
GROUP BY CONVERT(datetime, CONVERT(varchar, RowDate, 3))
ORDER BY TheDate DESC
union all
SELECT    SUM(Amount) AS Total, CONVERT(datetime, CONVERT(varchar, LogDate, 3)) AS TheDate
FROM         dbo.tbl_Register
GROUP BY CONVERT(datetime, CONVERT(varchar, LogDate, 3))
ORDER BY TheDate DESC
0
 
JohnHowlettAuthor Commented:
Thanks for the reply, but I need to compare the results against each other - hence having the results in the format:
Date....Total1....Total2

I was thinking about using another table containing a list of dates (or a function that produced a list of dates) , then I maybe able to 'GROUP BY'  the date from this table/function......
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Jai STech ArchCommented:
change it like this

SELECT theDate,dbo.GetTotal(TheDate),dbo.GetTotal2(TheDate)
FROM (SELECT CONVERT(datetime, CONVERT(varchar, RowDate, 3)) AS TheDate
FROM         dbo.tbl_Course
WHERE     (TypeID = 2)
GROUP BY CONVERT(datetime, CONVERT(varchar, RowDate, 3))
UNION ALL
SELECT    CONVERT(datetime, CONVERT(varchar, LogDate, 3)) AS TheDate
FROM         dbo.tbl_Register
GROUP BY CONVERT(datetime, CONVERT(varchar, LogDate, 3))) as A


The GetTotalfunction will have
Create function GetTotal(@date1 datetime)
returns int
as
select count(*) from dbo.tbl_Course where date = (@date1

and GetTotal1 as
Create function GetTotal(@date1 datetime)
returns int
as
select count(*) from dbo.tbl_Register where date = (@date1
0
 
JohnHowlettAuthor Commented:
Create function GetTotal(@date1 datetime)
returns int
as
select count(*) from dbo.tbl_Course where date = @date1


I get the Error Message:

Incorrect syntax near the keyword 'select'.
0
 
Jai STech ArchCommented:
Create function GetTotal(@date1 datetime)
returns int
as
begin
declare @ct int
select @ct = count(*) from dbo.tbl_Course where date = @date1
return @ct
end
0
 
JohnHowlettAuthor Commented:
Marvellous. A few edits here and there and I've got what I want! Thanks for your help

0
 
Jai STech ArchCommented:
I do  not understand why B grade was given...if there were something that are not clear in my answer you can very well query back...but other than unclear answers  - grade is to be given...you can check that in the HELP section of EE...you can change the grade by writing back to the moderator ....thnx
0
 
JohnHowlettAuthor Commented:
B: The Expert(s) provided an acceptable solution, or a link to an acceptable solution, that you were able to use, although you may have needed a bit more information to complete the task.

The solution provided was not correct and needed a number of adjustments. It was the start of the solution, hence a B grade.
0
All Courses

From novice to tech pro — start learning today.