CODEWITHSUNDEEP
sqlsql-servergroup-by
Marc
Marc

Reputation: 29

Combine separate queries

I have these four separate queries that I would like to consolidate into one result set and I'm not quite sure how to do it. Basically, I would like to see a single output with the following columns:

name - items_created - items_modified - copies_created - copies_modified

select t02CreatedBy as name, count(t02CreatedBy) as items_created
from dbo.Items_t02
where t02DateCreated > getdate() - 7
group by t02CreatedBy

select t02ModifiedBy as name, count(t02ModifiedBy) as items_modified
from dbo.Items_t02
where t02DateModified > getdate() - 7
group by t02ModifiedBy

select t03CreatedBy as name, count(t03CreatedBy) as copies_created
from dbo.Copies_t03
where t03DateCreated > getdate() - 7
group by t03CreatedBy

select t03ModifiedBy as name, count(t03ModifiedBy) as copies_modified
from dbo.Copies_t03
where t03DateModified > getdate() - 7
group by t03ModifiedBy

The tricky part for me is understanding how to combine these while still keeping the various groupings. I need to make sure that t02DateCreated is tied to t02CreatedBy and t02DateModifed is tied to t02ModifiedBy (etc...). Not sure how to do this in one query.

Any suggestions? Or am I going about this the wrong way?

Upvotes: 0

Views: 49

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Well, this is a way you could still do it in SQL-server (I haven't tested it though):

SELECT name,
      (select count(t02CreatedBy) from dbo.Items_t02
       where t02DateCreated>getdate()-7 and t02CreatedBy=name) createdItems
      (select count(t02ModifiedBy) from dbo.Items_t02
       where t02DateModified>getdate()-7 and t02ModifiedBy=name) modifiedItems
      (select count(t03CreatedBy) from dbo.Copies_t03
       where t03DateCreated>getdate()-7 and t03CreatedBy=name) createdCopies
      (select count(t03ModifiedBy) from dbo.Copies_t03
       where t03DateModified>getdate()-7 and t03ModifiedBy=name) modifiedCopies
FROM (    select t02CreatedBy name FROM dbo.Items_t02
union all select t02ModifiedBy     FROM dbo.Items_t02
union all select t03CreatedBy      FROM dbo.Copies_t03
union all select t03ModifiedBy     FROM dbo.Copies_t03   ) 
allnames GROUP BY name

The outer (grouped) query collects all the possible names, as they might appear in any of the four columns t02CreatedBy,t02ModifiedBy, t03CreatedBy or t03ModifiedBy. It then puts together the counts for each of these columns in the relevant tables by using the four subqueries.

As I don't know your data I used a UNION ALL-construct in the outer query. If you can guarantee for one of those columns (e.g. t02ModifiedBy) to actually contain "all possible" names, then it would also be OK to just use that column alone there, like:

...
FROM t02ModifiedBy FROM dbo.Items_t02 GROUP BY name

Upvotes: 0

Rich
Rich

Reputation: 26

Change the select statement to include something like this 

select **'Query 1' as Type**, t03ModifiedBy as name, count(t03ModifiedBy) as          copies_modified
from dbo.Copies_t03
where t03DateModified > getdate() - 7
group by t03ModifiedBy

and then add a 'Union All' between each query.

Upvotes: 1

Related Questions