Solved

Join two selects having group by

Posted on 2011-09-26
9
324 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS - Date Report Options 2 29
Problems "swapping" SQL Server DBs 2 22
sql server query 18 40
Where is the Help Section? 8 28
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 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