We help IT Professionals succeed at work.

Combining 2 SQL SUM queries with date as the join

JohnHowlett
JohnHowlett asked
on
1,447 Views
Last Modified: 2012-06-27
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?
Comment
Watch Question

Jai STech Arch

Commented:
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
CERTIFIED EXPERT

Author

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......
Jai STech Arch

Commented:
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
Tech Arch
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

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'.
Jai STech Arch

Commented:
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
CERTIFIED EXPERT

Author

Commented:
Marvellous. A few edits here and there and I've got what I want! Thanks for your help

Jai STech Arch

Commented:
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
CERTIFIED EXPERT

Author

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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.