asked on

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?

Jai S

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

JohnHowlett

ASKER

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 S

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

ASKER CERTIFIED SOLUTION

Jai S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnHowlett

ASKER

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 S

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

JohnHowlett

ASKER

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

Jai S

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

JohnHowlett

ASKER

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.