Solved

Join two selects having group by

Posted on 2011-09-26
9
295 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 31

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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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 31

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now