Solved

Join two selects having group by

Posted on 2011-09-26
9
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 39

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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