Solved

Join two selects having group by

Posted on 2011-09-26
9
331 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 39
populate value based on what is selected in lov 2 37
SQL- GROUP BY 4 25
MS SQL Server Management Studio R2 4 32
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

733 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