Solved

Join two selects having group by

Posted on 2011-09-26
9
316 Views
Last Modified: 2012-05-12
I'm trying to get the results of two select statements and combine them into one.
I have the following selects:
 
SELECT Account, count(*) as ct 
	FROM Table1 
	where version like 'x.0.0' and Account != '' and DATEDIFF(day, ReceivedDate,GETDATE()) < 30 
	group by Account 
	having count(*) >= 5 order by ct desc;

select Account, count(*) as ct_dev
	from Table1 t1
	left outer join Table2 t2 on t1.tr = t2.DevTr
	where DevTr is not null and account is not null and account <> '' and DATEDIFF(day, ReceivedDate,GETDATE()) < 30
	group by Account 
	 order by ct_dev desc;

Open in new window


They both have group by clause which is needed to get the count.
I can't figure out how to put together and outer join which can link the second select to the first select.
The common field is Account.

Can someone please show me how to do this?

Thanks
0
Comment
Question by:Axter
9 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 36599603
Are you looking for a total of the two counts or the two counts separately in one  query? Perhaps you can post some sample data from the two tables and what your expected output should be.
0
 
LVL 30

Author Comment

by:Axter
ID: 36599682
I'm looking for the two counts separately, so I would have a result like the following:
Account | ct | ct_dev

Table1 is in both select statements, and the second select statement does a left outer join to a second table (Table2).

Example Table1:
Account | TR | version | ReceivedDate
ABC Inc      050829      3.0.0      2005-08-29T14:53:11
ACME      060220      3.0.0      2006-02-20T15:22:48
FooFoo       060221      1.0.0      2006-02-21T00:42:38

Example Table2:
TR      DevTr
081013      050829
081031      090417
081105      060221
081110      090406
0
 
LVL 5

Expert Comment

by:almander
ID: 36599707
Will UNION work?
SELECT Account, count(*) as ct 
	FROM Table1 
	where version like 'x.0.0' and Account != '' and DATEDIFF(day, ReceivedDate,GETDATE()) < 30 
	group by Account 
	having count(*) >= 5 order by ct desc;
UNION ALL
select Account, count(*) as ct_dev
	from Table1 t1
	left outer join Table2 t2 on t1.tr = t2.DevTr
	where DevTr is not null and account is not null and account <> '' and DATEDIFF(day, ReceivedDate,GETDATE()) < 30
	group by Account 
	 order by ct_dev desc;

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 38

Accepted Solution

by:
Aaron Tomosky earned 225 total points
ID: 36599931
Make them both sub queries:
Select * from (first query here) as a
Left join (second query here) as b on a.account=b.account

Replace the * with the columns you want
0
 
LVL 30

Author Comment

by:Axter
ID: 36600219
>>Will UNION work?
Union was the first thing I tried, but it gives me an error stating incorrect syntax near keyword UNION.
I'm assuming you can't use the union in this context.

Also, I don't think it would work, because i believe you need a one to one relationship to get right output for the union.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 36600231
See the attached.
query.txt
0
 
LVL 30

Author Comment

by:Axter
ID: 36600304
>>Select * from (first query here) as a
>>Left join (second query here) as b on a.account=b.account

It looks like that works.  I just had to remove the order by, or rather move the order by to the outer select statement.

However, I'm seeing NULL value in ct_dev column when second select has no data to match the first select.
Is there a way I can make that list a 0 value instead?
Select a.Account as Account, ct, ct_dev from 
       (SELECT Account, count(*) as ct 
	FROM Table1 
	where version like 'x.0.0' and Account != '' and DATEDIFF(day, ReceivedDate,GETDATE()) < 30 
	group by Account 
	having count(*) >= 5

) as a
Left join 
(select Account, count(*) as ct_dev
	from Table1 t1
	left outer join Table2 t2 on t1.tr = t2.DevTr
	where DevTr is not null and account is not null and account <> '' and DATEDIFF(day, ReceivedDate,GETDATE()) < 30
	group by Account 
) 
as b on a.account=b.account order by ct desc;

Open in new window

0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 75 total points
ID: 36600475

as you have there, but try with...

Select a.Account as Account, ct, COALESCE(ct_dev,0) from ....etc
0
 
LVL 30

Author Closing Comment

by:Axter
ID: 36600500
Thank you all.
I now have exactly what I was looking for.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question