Counts from two tables?

Question

Post reply

Counts from two tables?

Jay Gamblin

SSC Eights!

Points: 884
More actions
November 20, 2008 at 4:49 pm

#201647

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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;

Jay Gamblin SSC Eights! Points: 884 More actions · Answer 3

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

John 1 1

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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;

Jay Gamblin SSC Eights! Points: 884 More actions · Answer 5

Ah that did the trick. Thanks so much for the help!!!

Jay Gamblin SSC Eights! Points: 884 More actions · Answer 6

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

Jay Gamblin SSC Eights! Points: 884 More actions · Answer 7

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

WITH

Users(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 1

Recursive common table expression 'Users' does not contain a top-level UNION ALL operator.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

Maybe this?

DECLARE @BDate DATETIME

DECLARE @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

SanjayAttray SSChampion Points: 13157 More actions · Answer 9

Thanks Mark. Though not same but I am about to write a similar script for one report and just saw your query.

SQL DBA.