Counts from two tables?
-
Sorry everyone. After my lesson in posting etiquette I've edited my post. Hope it makes the grade now.
I have two tables with seemingly unrelated information except for the users name like so...
create table dbo.Orders (
OrderID int,
UserName varchar(25),
OrderDate datetime
);
create table dbo.Tasks (
TaskID int,
UserName varchar(25),
TaskDate datetime
);
create table dbo.Users (
UserID int,
UserName varchar(25)
);
insert into dbo.Orders (OrderID, UserName, OrderDate)
select 1, 'John', '2008-01-01' union all
select 2,'John','2008-05-02' union all
select 3,'Jane','2008-03-03' union all
select 4,'Jill','2008-04-04' union all
select 5,'Jason','2008-07-05';
insert into dbo.Tasks (TaskID, UserName, TaskDate)
select 1,'John','2008-05-05' union all
select 2,'Jane','2008-04-06' union all
select 3,'Jill','2008-02-02' union all
select 4,'Jill','2008-02-15' union all
select 5,'Jen','2008-03-11';
insert into dbo.Users (UserID, UserName)
select 1,'John' union all
select 2,'Jane' union all
select 3,'Jill' union all
select 4,'Jen' union all
select 5, 'Jason';
select * from dbo.Orders;
select * from dbo.Tasks;
select * from dbo.Users;
OrderID UserName OrderDate
----------- ------------------------- -----------------------
1 John 2008-01-01 00:00:00.000
2 John 2008-05-02 00:00:00.000
3 Jane 2008-03-03 00:00:00.000
4 Jill 2008-04-04 00:00:00.000
5 Jason 2008-07-05 00:00:00.000
(5 row(s) affected)
TaskID UserName TaskDate
----------- ------------------------- -----------------------
1 John 2008-05-05 00:00:00.000
2 Jane 2008-04-06 00:00:00.000
3 Jill 2008-02-02 00:00:00.000
4 Jill 2008-02-15 00:00:00.000
5 Jen 2008-03-11 00:00:00.000
(5 row(s) affected)
UserID UserName
----------- --------------------------------------------------
1 John
2 Jane
3 Jill
4 Jen
5 Jason
(5 row(s) affected)
drop table dbo.Orders;
drop table dbo.Tasks;
drop table dbo.Users;
Now I need to show the counts of Orders and the counts of Tasks per user for a given date range. Like so...
UserName CountOfOrders CountOfMyTasks
-------------------------------------------------- ------------- --------------
Jane 1 1
Jason 1 0
Jen 0 1
Jill 1 2
John 2 1
I thought this would work but the counts are coming out wrong. (I've left out the date range for now to make it simpler)
SELECT Users.UserName, COUNT(Orders.OrderID) AS CountOfOrders, COUNT(Tasks.TaskID) AS CountOfTasks
FROM Users FULL OUTER JOIN
Orders ON Users.UserName = Orders.UserName FULL OUTER JOIN
Tasks ON Users.UserName = Tasks.UserName
GROUP BY Users.UserName
UserName CountOfOrders CountOfTasks
-------------------------------------------------- ------------- --------------
Jane 1 1
Jason 1 0
Jen 0 1
Jill 2 2
John 2 2
What am I doing wrong?
Any help would be apprieciated
-
This is what you should have provided us:
create table dbo.Orders (
OrderID int,
UserName varchar(25),
OrderDate datetime
);
create table dbo.Tasks (
TaskID int,
UserName varchar(25),
TaskDate datetime
);
insert into dbo.Orders (OrderID, UserName, OrderDate)
select 1, 'John', '2008-01-01' union all
select 2,'John','2008-05-02' union all
select 3,'Jane','2008-03-03' union all
select 4,'Jill','2008-04-04' union all
select 5,'Jason','2008-07-05';
insert into dbo.Tasks (TaskID, UserName, TaskDate)
select 1,'John','2008-05-05' union all
select 2,'Jane','2008-04-06' union all
select 3,'Jill','2008-02-02' union all
select 4,'Jill','2008-02-15' union all
select 5,'Jen','2008-03-11';
select * from dbo.Orders; -- show data entered
select * from dbo.Tasks; -- show data entered
--
-- Your code that isn't working would go here
--
drop table dbo.Orders;
drop table dbo.Tasks;
I haven't got an answer for you yet, but since I had done this much already, I thought I'd at least post it.
For more on how to ask for help, read the article linked in my signature block below.
-
Here is code that meets your requirements in your original post. I leave it to you to figure out how to add the date criteria to the query.
create table dbo.Orders (
OrderID int,
UserName varchar(25),
OrderDate datetime
);
create table dbo.Tasks (
TaskID int,
UserName varchar(25),
TaskDate datetime
);
insert into dbo.Orders (OrderID, UserName, OrderDate)
select 1, 'John', '2008-01-01' union all
select 2,'John','2008-05-02' union all
select 3,'Jane','2008-03-03' union all
select 4,'Jill','2008-04-04' union all
select 5,'Jason','2008-07-05';
insert into dbo.Tasks (TaskID, UserName, TaskDate)
select 1,'John','2008-05-05' union all
select 2,'Jane','2008-04-06' union all
select 3,'Jill','2008-02-02' union all
select 4,'Jill','2008-02-15' union all
select 5,'Jen','2008-03-11';
select * from dbo.Orders; -- show data entered
select * from dbo.Tasks; -- show data entered
--
-- Your code that isn't working would go here
--
with OrderCount (
UserName,
OrderCount
) as (
select
UserName,
Count(UserName)
from
dbo.Orders
group by
UserName
), TaskCount (
UserName,
TaskCount
) as (
select
UserName,
Count(UserName)
from
dbo.Tasks
group by
UserName
)
select
coalesce(oc.UserName, tc.UserName) as UserName,
coalesce(oc.OrderCount, 0) as CountOfOrders,
coalesce(tc.TaskCount, 0) as CountOfTasks
from
OrderCount oc
full outer join TaskCount tc
on (oc.UserName = tc.UserName);
drop table dbo.Orders;
drop table dbo.Tasks;
-
Thanks for the reply. It did get me the results I need but now that I try to get the date in there where I can add query for a date range, my attempt is grouping them by individual dates thus skewing the counts.
WITH OrderCount(UserName, OrderCount, OrderDate) AS
(SELECT UserName, COUNT(UserName) AS Expr1, OrderDate FROM Orders GROUP BY UserName, OrderDate),
TaskCount(UserName, TaskCount, TaskDate) AS
(SELECT UserName, COUNT(UserName) AS Expr1, TaskDate FROM MyTasks GROUP BY UserName, TaskDate)
SELECT COALESCE (oc.UserName, tc.UserName) AS UserName, COALESCE (oc.OrderCount, 0) AS CountOfOrders, COALESCE (tc.TaskCount, 0)
AS CountOfTasks
FROM OrderCount AS oc FULL OUTER JOIN
TaskCount AS tc ON oc.UserName = tc.UserName
UserName CountOfOrders CountOfTasks
------------------------- ------------- ------------
Jane 1 1
Jason 1 0
Jen 0 1
Jill 1 1
Jill 1 1
John 1 1
John 1 1
-
Hope the following (with changes) helps.
create table dbo.Orders (
OrderID int,
UserName varchar(25),
OrderDate datetime
);
create table dbo.Tasks (
TaskID int,
UserName varchar(25),
TaskDate datetime
);
insert into dbo.Orders (OrderID, UserName, OrderDate)
select 1, 'John', '2008-01-01' union all
select 2,'John','2008-05-02' union all
select 3,'Jane','2008-03-03' union all
select 4,'Jill','2008-04-04' union all
select 5,'Jason','2008-07-05';
insert into dbo.Tasks (TaskID, UserName, TaskDate)
select 1,'John','2008-05-05' union all
select 2,'Jane','2008-04-06' union all
select 3,'Jill','2008-02-02' union all
select 4,'Jill','2008-02-15' union all
select 5,'Jen','2008-03-11';
select * from dbo.Orders; -- show data entered
select * from dbo.Tasks; -- show data entered
--
-- Your code that isn't working would go here
--
declare @BDate datetime,
@EDate datetime;
set @BDate = '2008-01-01';
set @EDate = '2008-04-01';
select * from dbo.Orders where OrderDate >= @BDate and OrderDate < @EDate; -- show data entered
select * from dbo.Tasks where TaskDate >= @BDate and TaskDate < @EDate; -- show data entered
with OrderCount (
UserName,
OrderCount
) as (
select
UserName,
Count(UserName)
from
dbo.Orders
where
OrderDate >= @BDate
and OrderDate < @EDate
group by
UserName
), TaskCount (
UserName,
TaskCount
) as (
select
UserName,
Count(UserName)
from
dbo.Tasks
where
TaskDate >= @BDate
and TaskDate < @EDate
group by
UserName
)
select
coalesce(oc.UserName, tc.UserName) as UserName,
coalesce(oc.OrderCount, 0) as CountOfOrders,
coalesce(tc.TaskCount, 0) as CountOfTasks
from
OrderCount oc
full outer join TaskCount tc
on (oc.UserName = tc.UserName);
drop table dbo.Orders;
drop table dbo.Tasks;
-
Ah that did the trick. Thanks so much for the help!!!
-
By the way this worked great on my SQL 2005 server but I had a SQL 2000 server that was appearently not compatible with Common Table Expressions. So I found this article that helped me convert my query to use derived tables instead. http://msdn.microsoft.com/en-us/magazine/cc163346.aspx
-
I have an additional question about this. Now what if I wanted to create a table of users so I could see if one of my users had both zero Orders and zero Tasks. How do I join the users table to this?
Here is the users table
create table dbo.Users (UserID int,
UserName varchar(25)
);
insert into dbo.Users (UserID, UserName)
select 1, 'John' union all
select 2,'John' union all
select 3,'Jane' union all
select 4,'Jill' union all
select 5,'Jason' union all
select 6, 'Jay';
select * from dbo.Users; -- show data entered
I tried the following but did not work
WITHUsers(UserName) AS
(SELECT UserName
FROM Users
),
OrderCount(UserName, OrderCount) AS
(SELECT UserName, COUNT(UserName) AS Expr1
FROM Orders
GROUP BY UserName),
TaskCount(UserName, TaskCount) AS
(SELECT UserName, COUNT(UserName) AS Expr1
FROM Tasks
GROUP BY UserName)
SELECT
COALESCE (Users.UserName, OrderCount.UserName, TaskCount.UserName) AS UserName,
COALESCE (OrderCount.OrderCount, 0) AS CountOfOrders,
COALESCE (TaskCount.TaskCount, 0) AS CountOfTasks
FROM
Users
FULL OUTER JOIN
OrderCount ON Users.UserName = OrderCount.UserName
FULL OUTER JOIN
TaskCount ON Users.UserName = TaskCount.UserName
I get the following error
Msg 252, Level 16, State 1, Line 1Recursive common table expression 'Users' does not contain a top-level UNION ALL operator.
-
Maybe this?
DECLARE @BDate DATETIMEDECLARE @EDate DATETIME
SET @BDate = '20080101'
SET @EDate = '20080401'
SELECT u.UserName,
COUNT(DISTINCT o.OrderID ) AS CountOfOrders,
COUNT(DISTINCT t.TaskID ) AS CountOfTasks
FROM Users u
LEFT OUTER JOIN Orders o ON u.UserName = o.UserName AND o.OrderDate >= @BDate AND o.OrderDate = @BDate AND t.TaskDate < @EDate
GROUP BY u.UserName
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark. Though not same but I am about to write a similar script for one report and just saw your query.
SQL DBA.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply